Scan and fill empty cells with zero

[pban92]

Hi,

My excel file has 10 columns and different number of cells (rows). The largest column has 291 cells and the shortest column has 10 cells.

I need to write a macro that will scan all 10 columns until it reaches the 300th cell and fills empty cells with zero. So each column contain even number of cells.

Any help would be appreciated.

Many thanks!

 

[VoG]

Try

Sub Test2()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
On Error Resume Next
Range("A1:J" & LR).SpecialCells(xlCellTypeBlanks).Value = 0
On Error GoTo 0
End Sub

 

[kpark91]

Try this in your worksheet module

Sub FillZero()
Application.ScreenUpdating = False
For i = 1 To 10
    For j = 1 To 300
        If Cells(i, j).Value = "" Then
            Cells(i,j).Value = 0
        End If
    Next j
Next i
Application.ScreenUpdating = True
End Sub

 

[MickG]

Try:-

Range("A1:J300").SpecialCells(xlCellTypeBlanks).value = 0

Mick

 

[pban92]

Thanks all for your kind help!
@MickG, thanks for the magic!