VBA to remove any value of 0

[cbcarpenter]

I am new to using VBA's and I need help.

I need a VBA that will look through a spreadsheet in a specific range and clear any cell values of 0. The range would be Columns E,F,G,H,I,J,K,L all the way up to row 4000. Any help would be appreciated. Sorry i am very new to excel.

 

[lenze]

Hi and welcome to the board!!!
if you have 0's entered in your range, you can just use "Find and Replace" to remove them!
Find What: 0
Replace With: leave blank

lenze

 

[cbcarpenter]

I cant do that because the reason why the 0's are popping up is because I have other spreadsheets linked to this one so that when those are updated the master spreadsheet is updated but for some reason any blank cells in a linked spreadsheet will show up with 0's and when I go to find/replace I cant replace the 0's because it wont let me search the values only the formulas

 

[lenze]

Custom format your cells like this
#;#;

This will suppress the 0's in your sheets

lenze

 

[ashoengot]

try this dude

Sub ClearZero()
Dim x As Integer
x = 0
Do Until x = 10
x = x + 1
If Sheets("Sheet1").Range("A" & x).Value = 0 Then
Sheets("Sheet1").Range("A" & x).Value = ""
End If
Loop

End Sub


Note: This will also delete the formula in cells. Im Assuming that Column A has the Zero Values.

 

[cbcarpenter]

Thanks Lenze that worked!!

 

[mike015]

I was looking for a solution for this as well, after not finding exactly what I was looking for I came up with my own solution.
This removed all zero values either directly entered or as a result of a formula. It didn't affect zeros within the other formulas.
In testing it I did find it won't handle NA values so clean those up first.

Mike

Sub RemoveZeros()
'to remove 0 values that may be a result of a formula or direct entry.

For Each cell In Range("A1:D20")
 If cell.Value = "0" Then cell.Clear
 Next

End Sub