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