Macro to set blank cells for to zero for several columns

[zach9208]

I need help modifying this code. I want to set any blank values in column A to zero. This macro works for this but I have to specify the range. I want the range to select all of the data in the column. ie. if the last cell was A563 I want the macro to be only applying this change from cells A1:A563. I also have several other columns (C, F, G) that I want this macro to work on.

Sub test()

Dim cell As Range


For Each cell In Range("A1:D10")
    If Len(cell.Value) = 0 Then
        cell.Value = 0
    End If
Next


End Sub

 

[JLGWhiz]

like this

Sub test2()
Dim cell As Range
For Each cell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
    If Len(cell.Value) = 0 Then
        cell.Value = 0
    End If
Next
End Sub

 

[JLGWhiz]

this is actually faster if you have many rows and there are no formulas.

Sub t()
Range("A2", Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeBlanks) = 0
End Sub

 

[zach9208]

Neither one of these codes worked for me.

this is actually faster if you have many rows and there are no formulas.

Sub t()
Range("A2", Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeBlanks) = 0
End Sub