Automatically filling in 0 and 0% in Excel blank cells

I am trying to automatically enter 0 and 0% values in blank cells in Excel. I have a report in Excel that gets automatically filled up from SAS. After I save this report, I want the blank cells get automatically filled as 0 in numeric columns and 0% in percent columns.

What would the macro or VBA code be for that?

If you just want to add 0 to a blank cell, there are several ways to do that - here's one using range A1:D10 as an example. Note that if a cell is formatted as percentage, the "%" is automatically appended to the 0. :

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

Please note that if you are doing this on a large range of cells (actually it's good practice to do this all the time), you want to make sure you add Application.ScreenUpdating = False at the start of the code and Application.ScreenUpdating = True at the end. That will make it run much faster.

If the cells are truly blank (ie empty) then there are two ways to fill the cells immediately using SpecialCells, either manually, or with quick code avoiding loops.

David Mcritchie has written this up in detail here.

manual route

• Make a selection
• PressF5 ... Special .. Goto blanks
• in the formula bar add 0
• then press Ctrl + Enter

code route

Sub Quickfull()
'reset usedrange
ActiveSheet.UsedRange
On Error Resume Next
ActiveSheet.Cells.SpecialCells(xlBlanks).Value = 0
End Sub