I have mentioned before that blank cells in your data can affect Pivot Table defaults in Excel. They can also reduce the effectiveness of some keyboard and mouse shortcuts. The macro below populates blank cells in the selected range with zeros.

This macro also displays a message box if a single cell is selected. The macro requires a range to be selected.

Remember running a macro clears the undo list. This means the macro cannot be undone.

It is usually a good idea to save the file before running a macro. That way you can Close the file and not save it if errors occur.

Sub PopulateBlanks()
'this macro populates blanks cells in the selected range with zeros
 
On Error Resume Next
 
If TypeName(Selection) = "Range" Then
 
    If Selection.Rows.Count = 1 And Selection.Columns.Count = 1 Then
 
        MsgBox "Select a range before running the macro"
 
    Else
 
        Selection.SpecialCells(xlCellTypeBlanks).Value = "0"
 
    End If
 
End If
 
End Sub

The On Error Resume Next command is used in case the range doesn’t have any blank cells.

If no blanks are found then the SpecialCells command macro would return an error message.

The TypeName check ensures that a range is selected.

Comparing the row and column count of the selected range to one is an easy way to identify if a single cell is selected.

Click here to see instructions on how to install and run macros.