Fill in blanks cells with 0

[bradyj7]

Hi there,

Would anyone have a piece of code that would find all blanks in a used range and fill them with zero. The number of columns is fixed (D-G) but the number of rows is variable.

Thank you

 

[VoG]

Try

Sub Test2()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("D1:G" & LR).SpecialCells(xlCellTypeBlanks).Value = 0
End Sub

 

[Peter_SSs]

Do you need code?

1. Select columns D:G by click & drag across the column labels.

2. Ctrl+H|Find what: leave blank|Replace with: 0|Replace All


If you do need code

Sub FillBlanks()
    On Error Resume Next
    Columns("D:G").SpecialCells(xlCellTypeBlanks).Value = 0
    On Error GoTo 0
End Sub

If you are certain there are going to be blanks, you wouldn't need the two 'On Error' lines

 

[bradyj7]

Thanks that works