How to Delete Empty Rows in VBA (With Examples)

You can use the following methods in VBA to delete empty rows:

Method 1: Delete Empty Rows in Specific Range

Sub DeleteEmptyRowsInRange()
    Sheets("Sheet1").Select
    Range("A1:B10").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

This particular macro will delete all empty rows in the range A1:B10 of Sheet1.

Method 2: Delete Empty Rows in Entire Sheet

Sub DeleteEmptyRowsInSheet()
  
    'turn off screen updating for faster performance
    Application.ScreenUpdating = False
  
    Dim i As Long

    With ActiveSheet

    For i = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
        If WorksheetFunction.CountA(.Rows(i)) = 0 Then
            ActiveSheet.Rows(i).Delete
        End If
    Next

    End With
  
    'turn screen updating back on
    Application.ScreenUpdating = True
  
End Sub

This particular macro will delete all empty rows in the entire active sheet.

The following examples show how to use each method in practice.

Example 1: Delete Empty Rows in Specific Range

Suppose we have the following dataset in Excel that contains information about various basketball players:

We can create the following macro to delete all empty rows in the range A1:B10 on this sheet:

Sub DeleteEmptyRowsInRange()
    Sheets("Sheet1").Select
    Range("A1:B10").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Once we run this macro, we receive the following output:

Notice that all empty rows have been deleted from the range that we specified.

Example 2: Delete Empty Rows in Entire Sheet

Suppose we have the following dataset in Excel that contains information about various basketball players:

We can create the following macro to delete all empty rows in the entire sheet:

Sub DeleteEmptyRowsInSheet()
  
    'turn off screen updating for faster performance
    Application.ScreenUpdating = False
  
    Dim i As Long

    With ActiveSheet

    For i = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
        If WorksheetFunction.CountA(.Rows(i)) = 0 Then
            ActiveSheet.Rows(i).Delete
        End If
    Next

    End With
  
    'turn screen updating back on
    Application.ScreenUpdating = True
  
End Sub

Once we run this macro, we receive the following output:

Notice that all empty rows have been deleted from the entire sheet.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Delete Rows Based on Cell Value
VBA: How to Delete Sheet if Name Contains Specific Text
VBA: How to Delete Files