Delete Blank Rows With Excel VBA

VBA Code to Delete Blank Rows

Sub DeleteBlankRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim rng As Range
    ' Set the active worksheet
    Set ws = ActiveSheet
    ' Find the last row with data in the worksheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Loop from the last row to the first row (to avoid skipping rows)
    For r = lastRow To 1 Step -1
        ' Check if the entire row is empty
        If Application.WorksheetFunction.CountA(ws.Rows(r)) = 0 Then
            ws.Rows(r).Delete
        End If
    Next r
    ' Release memory
    Set ws = Nothing
End Sub

Detailed Explanation of the Code

1. Declaring Variables

Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
Dim rng As Range

• ws: Stores the reference to the worksheet.
• lastRow: Stores the last used row in the worksheet.
• r: Used as a counter to iterate through rows.
• rng: (Not used in this example but can be useful for range selection).

2. Assign the Active Worksheet

Set ws = ActiveSheet

• This assigns the currently active worksheet to the variable ws, ensuring we operate on the correct sheet.

3. Find the Last Used Row

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• ws.Rows.Count returns the total number of rows in the sheet (e.g., 1,048,576 for Excel 2007+).
• .End(xlUp) moves upwards from the last row in column A until it finds a non-empty cell.
• This technique effectively finds the last used row in the worksheet.

4. Loop Through Rows (From Bottom to Top)

For r = lastRow To 1 Step -1

• We iterate backward from lastRow to row 1 (Step -1 ensures no row is skipped).
• If we looped from top to bottom, deleting rows would shift the row numbers, causing some blank rows to be missed.

5. Check If the Row is Blank

If Application.WorksheetFunction.CountA(ws.Rows(r)) = 0 Then

• CountA(ws.Rows(r)) counts the number of non-empty cells in the entire row.
• If the result is 0, it means the row is completely empty.

6. Delete the Blank Row

ws.Rows(r).Delete

• Deletes the entire row when it is found to be blank.

7. Release Memory

Set ws = Nothing

• This is good practice to free up memory after executing the macro.

Alternative Approach Using AutoFilter

This method is faster for large datasets.

Sub DeleteBlankRowsWithFilter()
    Dim ws As Worksheet
    Dim rng As Range
    ' Set the worksheet
    Set ws = ActiveSheet
    ' Set the range covering all used rows
    On Error Resume Next
    Set rng = ws.UsedRange
    On Error GoTo 0
    ' Check if the range is valid
    If Not rng Is Nothing Then
        ' Apply filter to find blank rows in column A (change as needed)
        rng.AutoFilter Field:=1, Criteria1:="="  
        ' Delete visible rows after filtering
        On Error Resume Next
        ws.Rows("2:" & ws.Rows.Count).SpecialCells(xlCellTypeVisible).Delete
        On Error GoTo 0    
        ' Turn off filter
        ws.AutoFilterMode = False
    End If
    ' Release memory
    Set ws = Nothing
End Sub

Advantages of AutoFilter Method

Faster on large datasets
Avoids looping through each row
Works efficiently with large spreadsheets

Conclusion

• For small datasets, the first method (looping through rows) works well.
• For large datasets, the AutoFilter method is much faster and more efficient.