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.