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 function 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.