Excel Macro: Delete Blank or Empty Rows

Blank rows can often cause problems with formulas. If you find that you are manually searching out and deleting blank rows in your data sets, this simple macro can help automate delete empty rows. If you want to delete blank or empty columns, click here.

Delete Blank or Empty Rows

'------------------ Modules ------------------
Sub DeleteBlankRows()
'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 WorksheetFunction.CountA(Rows(iCounter).EntireRow) = 0 Then
            Rows(iCounter).Delete
        End If
'Step 5: Increment the counter down
    Next iCounter
End Sub

How This Macro 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.

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("A4:A12").Rows(4) points to the forth row in the range A4:A12. 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 This Macro