VBA - Zero where Blank

[garypea123]

Good Morning,

I am sent a file which is similar to the below but I would like to know if there is a quick way that between D3 and J (Rows decrease / increase each day depending on data) that I can run a quick VBA which will add a 0 if the cell is blank?

Thanks,
Gary

 

[alansidman]

How about using Find and Replace built in function in Excel. No macro needed.

 

[garypea123]

I think its because one week there will be 1000 lines, another week 1200 lines, then 800 lines. I am trying to figure out a way of automating the process so someone has to click a button.

If possible I would like to avoid any manual work at all, because the idea is someone will just a click a button. My issue is that the rest of my macro assumes there are zero's and not blanks.

 

[Akuini]

Try:

Sub a1159010a()
Dim i As Long
i = Range("B" & Rows.Count).End(xlUp).Row
Range("D3:J" & i).SpecialCells(xlCellTypeBlanks).Value = 0
End Sub

edit: i changed
Range("A" & Rows.Count).End(xlUp).Row
to
Range("B" & Rows.Count).End(xlUp).Row
because col A is empty

 

[garypea123]

Dim i As Long i = Range("B" & Rows.Count).End(xlUp).Row Range("D3:J" & i).SpecialCells(xlCellTypeBlanks).Value = 0

Perfect, works like a dream, and I understand the script

 

[Akuini]

Glad it works.