Macro 37: Deleting Blank Rows
Work with Excel long enough, and you’ll find out that blank rows can often cause havoc on many
levels. They can cause problems with formulas, introduce risk when copying and pasting, and some-
times cause strange behaviors in PivotTables. If you find that you are manually searching out and
deleting blank rows in your data sets, this macro can help automate that task.
How it works
In this macro, we are using the UsedRange property of the Activesheet object to define the
range we are working with. The UsedRange property gives us a range that encompasses the cells
that have been used to enter data. We then establish a counter that starts at the last row of the used
range to check if the entire row is empty. If the entire row is indeed empty, we remove the row. We
keep doing that same delete for every loop, each time incrementing the counter to the previous row.
Sub Macro37()
‘Step1: Declare your variables.
Dim MyRange As Range
Dim iCounter As Long
‘Step 2: Define the target Range.
Set MyRange = ActiveSheet.UsedRange
‘Step 3: Start reverse looping through the range.
For iCounter = MyRange.Rows.Count To 1 Step -1
‘Step 4: If entire row is empty then delete it.
If Application.CountA(Rows(iCounter).EntireRow) = 0 Then
Rows(iCounter).Delete
End If
‘Step 5: Increment the counter down
Next iCounter
End Sub
1. The macro first declares two variables. The first variable is an Object variable called
MyRange. This is an object variable that defines our target range. The other variable
is a Long Integer variable called iCounter. This variable serves as an incremental
counter.
2. In Step 2, the macro fills the MyRange variable with the UsedRange property of the
ActiveSheet object. The UsedRange property gives us a range that encompasses the
cells that have been used to enter data. Note that if we wanted to specify an actual range or
a named range, we could simply enter its name — Range(“MyNamedRange”).
3. In this step, the macro sets the parameters for the incremental counter to start at the max
count for the range (MyRange.Rows.Count) and end at 1 (the first row of the chosen
range). Note that we are using the Step-1 qualifier. Because we specify Step -1, Excel
knows we are going to increment the counter backwards, moving back one increment on
each iteration. In all, Step 3 tells Excel to start at the last row of the chosen range, moving
backward until it gets to the first row of the range.
4. When working with a range, you can explicitly call out a specific row in the range by passing
a row index number to the Rows collection of the range. For instance,
Range(“D6:D17”).Rows(5) points to the fifth row in the range D6:D17.
In Step 4, the macro uses the iCounter variable as an index number for the Rows collection
of MyRange. This helps pinpoint which exact row we are working with in the current loop.
The macro checks to see whether the cells in that row are empty. If they are, the macro
deletes the entire row.
5. In Step 5, the macro loops back to increment the counter down.
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it is named personal.xlb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
If you don’t see personal.xlb in your project window, it means it doesn’t exist yet. You’ll have to
record a macro, using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In
drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the
recorded macro and replace it with this one.
