Excel Delete Blank Rows

This Excel tutorial explains how to delete blank rows in Excel with VBA and without VBA.

You may also want to read:

Excel delete blank rows and blank cells before import into Access

Excel delete duplicated data in consecutive rows

Excel Delete Blank Rows

Sometimes we have data that contains blank rows, one example is data with pivot table layout where data are grouped together and repeated items are blank.

Previously I wrote a post about filling blank rows, this post is about delete blank rows.

Excel Delete Blank Rows (non VBA)

Assume that our worksheet contains blank rows in yellow.

excel remove blank rows 01

 

Select the cells

excel remove blank rows 02

 

Press F5 to open the “Go To” box.  (or navigate to Home > Find & Select > Go to)

Press “Special” button

excel remove blank rows 03

 

Select “Blanks” > OK

excel remove blank rows 04

 

Navigate to Home > Delete > Delete Sheet Rows

excel remove blank rows 05

 

All blank rows are removed

excel remove blank rows 06

Note that there could be cells that appear to be blank but you fail to select them using Go To, one common reason is that the data comes from another system and export as Excel. In that case you need to click on that particular cell and press delete.

Excel Delete Blank Rows based on 1 column (VBA)

As spoken above, there could be cases where you cannot select blank Cell, then you need to redefine the definition of “blank”.

One common way to check blank Cell is using VBA IsEmpty Function. Technically IsEmpty is used to identify uninitialized Cell and return TRUE /FALSE, formatting the Cell such as color is not considered as initializing the Cell. For the case of “initialized” blank cells as well as cells that contain space, we can use the combination of Trim and Len as below.

Len(Trim(cell)) = 0

Below is an example how to remove blank rows using VBA in column A.

Public Sub remove_EmptyCell()
    For r = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1  
        If IsEmpty(Range("A" & r)) Or Len(Trim(Range("A" & r).Value)) = 0 Then
            Range("A" & r).EntireRow.Delete
        End If
    Next r  
End Sub

Excel Delete Blank Rows based on usedRange (VBA)

The below procedure make uses of the property of usedRange to delete blank rows as well as blank columns. This procedure is able to handle abnormally blank rows due to exporting data from other systems.

Public Sub delete_empty_row()
    Application.ScreenUpdating = False

    For Each usedrng In ActiveSheet.UsedRange
        If usedrng.MergeCells = True Then
            If usedrng.Value = "" Then
                usedrng.Value = ""
            End If
        Else
            If usedrng.Value = "" Then
                usedrng.ClearContents
            End If
        End If
    Next
    
    ActiveSheet.UsedRange
    usedRangeLastColNum = ActiveSheet.UsedRange.Columns.Count
    usedrangelastrow = ActiveSheet.UsedRange.Rows.Count
    
    For r = usedrangelastrow To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(r, usedRangeLastColNum).EntireRow) <> 0 Then
            Exit For
        Else
            Cells(r, usedRangeLastColNum).EntireRow.Delete
        End If
    Next r
    
    For c = usedRangeLastColNum To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(1, c).EntireColumn) <> 0 Then
            Exit For
        Else
            Cells(1, c).EntireColumn.Delete
        End If
    Next c
    
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Sub

 

Leave a Reply

Your email address will not be published.