Macro 52: Replace Blanks Cells with a Value
In some analyses, blank cells can cause of all kinds of trouble. They can cause sorting issues, they can
prevent proper auto filling, they can cause your pivot tables to apply the Count function instead of
the Sum function, and so on.
Blanks aren’t always bad, but if they are causing you trouble, this is a macro you can use to quickly
replace the blanks in a given range with a value that indicates a blank cell.
How it works
This macro enumerates through the cells in the given range, and then uses the Len function to
check the length of the value in the active cell. Blank cells have a character length of 0. If the length is
indeed 0, the macro enters a 0 in the cell, effectively replacing the blanks.
Sub Macro52()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells?
Select Case MsgBox(“Can’t Undo this action. ” & _
“Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
‘Step 3: Define the target Range.
Set MyRange = Selection
‘Step 4: Start looping through the range.
For Each MyCell In MyRange
‘Step 5: Ensure the cell has Text formatting.
If Len(MyCell.Value) = 0 Then
MyCell = 0
End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. We first declare two Range object variables, one called MyRange to hold the entire target
range, and the other called MyCell to hold each cell in the range as the macro enumerates
through them one by one.
2. When you run a macro, it destroys the undo stack. This means you can’t undo the changes a
macro makes. Because we are actually changing data, we need to give ourselves the option
of saving the workbook before running the macro. This is what Step 2 does. Here, we call up
a message box that asks if we want to save the workbook first. It then gives us three choices:
Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking
Cancel exits the procedure without running the macro. Clicking No runs the macro without
saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we are using the
selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target
range is a named range, you can simply enter its name: Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell.
5. After a cell is activated, we use the IsEmpty function to make sure the cell is not empty. We
do this to save a little on performance by skipping the cell if there is nothing in it. We then
use the Len function, which is a standard Excel function that returns a number correspond-
ing to the length of the string being evaluated. If the cell is blank, the length will be 0, at
which point, the macro replaces the blank with a 0. You could obviously replace the blank
with any value you’d like (N/A, TBD, No Data, and so on).
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the
macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
