Removing the Zeros from Blank Cells

[LordDragon]

Greetings,


I have some code that fills a sheet in my workbook from another workbook (customer filled form). This part works great. However, if the original file has any blanks, when I run my code and fill in the workbook the blanks end up as "0".

I'm trying to make a code that will go through that form after that and remove all the zeros.

This is the code I'm trying:




Function RigSurveyFormCleanUp()
'Clears all the form fields that are not actually used.


Application.ScreenUpdating = False

'Determine how many rows in the Order Summary sheet need copied.
With ActiveWorkbook.Worksheets("Rig Survey Form")

CellsToCheck = Array("D4", "N4", "C6", "M6", "C8", "M8", "C12", "J12", "R12", "C14", "I14", "N14", "C16", "K16", "R16", "C18", "I18", "Q18", "D20", "Q20", "D24", "L24", "E26", "L26", "E28", "L28", "E30", "D34", "A36", "D38", "A40", "C44", "A46", "E48", "K48", "C50", "H50", "M50", "R50", "C52", "H52", "M52", "R52", "C54", "H54", "M54", "R54", "C56", "H56", "M56", "R56", "C58", "H58", "M58", "R58", "C60", "H60", "M60", "R60", "C62", "H62", "M62", "R62", "C64", "H64", "M64", "R64", "J66", "A68")


For Each cll In Range(CellsToCheck)
If cll.Value = 0 Then ClearContents


Next cll


End With

Application.ScreenUpdating = True

End Function



I keep getting a Compile Error at the ClearContents part.

I would appreciate any help I can get with this.

[SamT]

Either you have Excel Options set to show Zero Values, in which case they will always be zeros, or you aren't transferring the data correctly.


If not value = 0 then
tranfser data

[snb]

That's because you use a Function where you should apply a Sub.

[mikerickson]

Try

For Each cll In Range(CellsToCheck)
If cll.Value = 0 Then cll.ClearContents
Next cll

I'm also not sure about your Range(Array("D5", "N4")) formulation.

You might try (add more cells to the Set statement) (Note also the dot before the .Range that is missing from the OP code)

Function RigSurveyFormCleanUp()
Dim CellsToCheck As Range
Dim cll As Range
'Clears all the form fields that are not actually used.


Application.ScreenUpdating = False

'Determine how many rows in the Order Summary sheet need copied.
With ActiveWorkbook.Worksheets("Rig Survey Form")

Set CellsToCheck = .Range("D4, N4, C6, M6, C8, M8, C12")

For Each cll In CellsToCheck.Cells
If cll.Value = 0 Then cll.ClearContents
Next cll


End With

Application.ScreenUpdating = True
End Function

This will work find when called by VBA, but if you are using this as a function that is called from a worksheet formula, it will not ClearContents and will return a #VALUE error.

UDF's called by worksheet formulas can not change the environment (color cells, write to cells, clear cells, select cells....). They can only return a value.

[LordDragon]

mikerickson,

Thank you for the response. I tried your method, but I'm still getting an error on the range of CellsToCheck.

The reason for the range is because this page is an exact duplicate of the form we send the customer. So it is laid out like a form you would expect to fill out by hand.

If it is easier to figure out which cells to check, they are all "unprotected" but the rest of the cells on the page are protected. I'm not sure if there is just a way to check all the unprotected cells and clear them if they read "0".

[Paul_Hossler]

Thoughts ..

1. Add Option Explicit to the top of the module (all modules)
2. CellsToCheck is an array of strings, so I don't think you can make it a Range that way
3. ClearContents is a method on the Range object, so IF cll had been a Range, you would need cll.ClearContents (you didn't that the 'dot' and object)


Try this and see if it moves you farther along





Option Explicit

Sub RigSurveyFormCleanUp()
Dim CellsToCheck As Variant, cll As Variant

Application.ScreenUpdating = False

'Determine how many rows in the Order Summary sheet need copied.
With ActiveWorkbook.Worksheets("Rig Survey Form")

CellsToCheck = Array("D4", "N4", "C6", "M6", "C8", "M8", "C12", "J12", "R12", "C14", "I14", "N14", "C16", "K16", "R16", "C18", "I18", "Q18", "D20", "Q20", "D24", "L24", "E26", "L26", "E28", "L28", "E30", "D34", "A36", "D38", "A40", "C44", "A46", "E48", "K48", "C50", "H50", "M50", "R50", "C52", "H52", "M52", "R52", "C54", "H54", "M54", "R54", "C56", "H56", "M56", "R56", "C58", "H58", "M58", "R58", "C60", "H60", "M60", "R60", "C62", "H62", "M62", "R62", "C64", "H64", "M64", "R64", "J66", "A68")


For Each cll In CellsToCheck

With .Range(cll)
If .Value = 0 Then .ClearContents
End With

Next cll


End With

Application.ScreenUpdating = True

End Sub

[LordDragon]

Paul,

Thanks for the suggestion. I gave it a try and it did get me further, but not much.

I'm still getting a "debug" window and it highlights the .ClearContents method.

I tried even removing the If .Value = 0 part and make it just fill in the form, then clear it, but I still got the error on the .ClearContents part.

[Paul_Hossler]

Post your code

The fragment I posted in #6 ran ok

Edit: are any of those cell part of a merged cell?

[LordDragon]

I've actually been playing with it and figured something out. All the the cells in the range are actually Merged. My original code for resetting the page actually was ridiculous (I'm still learning Arrays so I write long code first, then try to convert it later).

Anyway, I took that code and modified yours to include the fact that the cells are merged.

Here is my new page reset code:




Function RigSurveyFormClear()
'Clears all the form fields that are not actually used.


Dim CellsToCheck As Variant
Dim cll As Variant


Application.ScreenUpdating = False


'Determine how many rows in the Order Summary sheet need copied.
With ActiveWorkbook.Worksheets("Rig Survey Form")

CellsToCheck = Array("D4:I4", "N4:S4", "C6:I6", "M6:S6", "C8:I8", "M8:S8", "C12:E12", "J12:K12", "R12:S12", "C14:D14", "I14:J14", "N14:P14", "C16:E16", "K16:M16", "R16:S16", "C18:D18", "I18:J18", "Q18:R18", "D20:H20", "Q20:R20", "D24:F24", "L24:N24", "E26:F26", "L26:N26", "E28:F28", "L28:N28", "E30:F30", "D34:T34", "A36:T36", "D38:T38", "A40:T40", "C44:T44", "A46:T46", "E48:F48", "K48:L48", "C50:D50", "H50:I50", "M50:N50", "R50:S50", "C52:D52", "H52:I52", "M52:N52", "R52:S52", "C54:D54", "H54:I54", "M54:N54", "R54:S54", "C56:D56", "H56:I56", "M56:N56", "R56:S56", "C58:D58", "H58:I58", "M58:N58", "R58:S58", "C60:D60", "H60:I60", "M60:N60", "R60:S60", "C62:D62", "H62:I62", "M62:N62", "R62:S62", "C64:D64", "H64:I64", "M64:N64", "R64:S64", "J66:T66", "A68:T68")


For Each cll In CellsToCheck
With .Range(cll)
.ClearContents
End With


Next cll


End With


Application.ScreenUpdating = True


End Function


This code works great for resetting the page, I've even already changed my project to actually use it.

However, when I try to use the code that checks for zeros I get a Debug box and it highlights the "If .Value = 0 Then" part.





Function RigSurveyFormCleanUp()
'Clears all the form fields that are not actually used.


Dim CellsToCheck As Variant
Dim cll As Variant


Application.ScreenUpdating = False


'Determine how many rows in the Order Summary sheet need copied.
With ActiveWorkbook.Worksheets("Rig Survey Form")

CellsToCheck = Array("D4:I4", "N4:S4", "C6:I6", "M6:S6", "C8:I8", "M8:S8", "C12:E12", "J12:K12", "R12:S12", "C14:D14", "I14:J14", "N14:P14", "C16:E16", "K16:M16", "R16:S16", "C18:D18", "I18:J18", "Q18:R18", "D20:H20", "Q20:R20", "D24:F24", "L24:N24", "E26:F26", "L26:N26", "E28:F28", "L28:N28", "E30:F30", "D34:T34", "A36:T36", "D38:T38", "A40:T40", "C44:T44", "A46:T46", "E48:F48", "K48:L48", "C50:D50", "H50:I50", "M50:N50", "R50:S50", "C52:D52", "H52:I52", "M52:N52", "R52:S52", "C54:D54", "H54:I54", "M54:N54", "R54:S54", "C56:D56", "H56:I56", "M56:N56", "R56:S56", "C58:D58", "H58:I58", "M58:N58", "R58:S58", "C60:D60", "H60:I60", "M60:N60", "R60:S60", "C62:D62", "H62:I62", "M62:N62", "R62:S62", "C64:D64", "H64:I64", "M64:N64", "R64:S64", "J66:T66", "A68:T68")


For Each cll In CellsToCheck
With .Range(cll)
If .Value = 0 Then .ClearContents
End With


Next cll


End With


Application.ScreenUpdating = True

End Function



Some of the fields I'm trying to clear are showing "0.00" and some are showing "0". I don't think that matters, but I'm trying to make sure you have all the information.

[Paul_Hossler]

oops

missed something

[Paul_Hossler]

try this. It should work if the ranges are merged cells or not




Option Explicit

Sub RigSurveyFormCleanUp()
Dim CellsToCheck As Variant
Dim cll As Variant
Dim r As Range


Application.ScreenUpdating = False


'Determine how many rows in the Order Summary sheet need copied.
With ActiveWorkbook.Worksheets("Rig Survey Form")

CellsToCheck = Array("D4:I4", "N4:S4", "C6:I6", "M6:S6", "C8:I8", "M8:S8", "C12:E12", "J12:K12", "R12:S12", "C14:D14", "I14:J14", "N14:P14", "C16:E16", "K16:M16", "R16:S16", "C18:D18", "I18:J18", "Q18:R18", "D20:H20", "Q20:R20", "D24:F24", "L24:N24", "E26:F26", "L26:N26", "E28:F28", "L28:N28", "E30:F30", "D34:T34", "A36:T36", "D38:T38", "A40:T40", "C44:T44", "A46:T46", "E48:F48", "K48:L48", "C50:D50", "H50:I50", "M50:N50", "R50:S50", "C52:D52", "H52:I52", "M52:N52", "R52:S52", "C54:D54", "H54:I54", "M54:N54", "R54:S54", "C56:D56", "H56:I56", "M56:N56", "R56:S56", "C58:D58", "H58:I58", "M58:N58", "R58:S58", "C60:D60", "H60:I60", "M60:N60", "R60:S60", "C62:D62", "H62:I62", "M62:N62", "R62:S62", "C64:D64", "H64:I64", "M64:N64", "R64:S64", "J66:T66", "A68:T68")


For Each cll In CellsToCheck
With .Range(cll)
If .MergeCells Then
If .Cells(1, 1).Value = 0 Then .ClearContents
Else
For Each r In .Cells
If r.Value = 0 Then r.ClearContents
Next
End If
End With
Next
End With
End Sub

[LordDragon]

That fixed it. Thanks.

[Paul_Hossler]

OK

Sorry it took so long