Table of Contents
To delete empty rows in VBA, you can use a simple loop to check each row in the worksheet to determine if it is empty or not. If the row is empty, the code deletes the row. If the row is not empty, the code moves on to the next row. This process is repeated until all empty rows are deleted from the worksheet.
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
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.