How To Delete Blank Rows [Excel VBA] – Skills and Automation

This tutorial will cover how to delete blank or empty rows using Excel VBA. We will cover two techniques:

SpecialCells
Reverse For Loop

We will also, look at how to delete rows when a cell within the column is blank.

SpecialCells: Intro

SpecialCells is a Method that returns a Range object i.e. a cell or a group of cells based on the parameter that we supply to that method. The parameter that we are particularly interested is the xlCellTypeBlanks which returns all the blank cells within the range that we have selected.

The range can be either the entire worksheet

.Cells.SpecialCells(xlCellTypeBlanks).Select

Or, a specific data range

.Range("A1:X500").SpecialCells(xlCellTypeBlanks).Select

Or, even a column

.Columns(1).SpecialCells(xlCellTypeBlanks).Select

In all these examples, we have just selected the blank cells within the specified range. But, we can even go ahead and delete the row in which each of those blank cells are located.

We will just replace each select with EntireRow.Delete.

SpecialCells: Code to Delete

Consider the above dataset with the blank rows in yellow. We can use the SpecialCells method to go ahead and delete all the rows associated with a blank cell.

Sub DeleteBlankRows_SpecialCells()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

ws.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

SpecialCells: Error Handling

The SpecialCells method returns a Range object. This brings us to our first issue with SpecialCells. If there are no blank cells, the macro will throw a Run Time error. We can use a Resume Next statement to skip over the code, if that happens.

Sub DeleteBlankRows_SpecialCells()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

On Error Resume Next
ws.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

End Sub

SpecialCells: Issue

However, our next issue may be a deal breaker. A blank cell doesn’t necessarily mean that the entire row is blank. SpecialCells will delete the row regardless. And, this is something you may not want.

So, when do we use this SpecialCells method? If you are absolutely sure that your dataset may contain blank rows, but not necessarily blank cells within a populated row, then go ahead and use this method. Its much faster then the next alternative we are going to look at.

SpecialCells: Delete Row if Blank Cell in Column

Consider the above dataset, where we want to delete any row with a blank cell in Column D. This is probably the best way to use this method.

Sub DeleteBlankRows_SpecialCells()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

On Error Resume Next
ws.Cells.Columns("D").EntireRow.Delete
On Error GoTo 0

End Sub

Reverse For Loop: Intro

The most common way to loop over a dataset is from top to bottom. But, when deleting rows, we need to loop from bottom to top. Why?

Let’s delete row 3 in Excel. What happens to the dataset below. It moves up. The line that was previously row number 4, is now row number 3. If we are doing this via a top-to-bottom For loop, we would have deleted row 3 and would now, be moving on to the next row number. But, because the dataset has moved up, we would move on to row 4 which is actually row 5 of the original dataset. This means we will effectively have skipped over the next original row whenever we delete a line via a loop. Hence, while deleting rows, we will follow a bottom-to-top For Loop.

Loop: Code to Delete

We need to cover two more points.

Last Row

To loop over a dataset, we need to find the last row. Since our dataset may contain blank rows, the best way to find the last row is via the UsedRange technique.

CountA function

Our next question is how do we determine if the row has blank or empty cells. We could use the Excel CountA function in VBA. If there a no non-blank cells in a row, the function will return a zero, which means that the row is blank.

Code to delete above highlighted blank rows is as follows.

Sub DeleteBlankRows_ForLoop()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

Dim lrow As Long
lrow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row

Dim i As Long
For i = lrow To 1 Step -1
    
    If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
        ws.Rows(i).Delete
    End If
Next i

End Sub

Loop: Improve Speed

The For Loop is slower than the SpecialCells method, especially over large datasets.

Two ways to improve speed are:

Turn off Screen Updating

Each time VBA interacts with Excel, the screen flutters. To avoid this turn-off screen updating at the start of the macro, and turn it back on at the end. This will keep the screen steady and improve speed as well.

Turn off Calculation

If there are formulas in the dataset, Excel will recalculate all the formulas each time a row is deleted. This will slow down the macro. To avoid this, change calculations to Manual at the start of the macro, and turn it back to automatic at the end.

Sub DeleteBlankRows_ForLoop()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

Dim lrow As Long
lrow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row

Dim i As Long
For i = lrow To 1 Step -1
    
    If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
        ws.Rows(i).Delete
    End If
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Loop: Delete Row if Blank Cell in Columns

We can even loop over a column, and delete the row if the cell is blank or empty. We can use the CountA, or directly check if the cell value is blank (“”) or the length of the cell value is 0.

Sub DeleteBlankRows_ForLoop()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

Dim lrow As Long
lrow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row

Dim i As Long
For i = lrow To 1 Step -1
    
    If ws.Cells(i, 4) = "" Then
        ws.Rows(i).Delete
    End If
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Home