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.
Select the cells
Press F5 to open the “Go To” box. (or navigate to Home > Find & Select > Go to)
Press “Special” button
Select “Blanks” > OK
Navigate to Home > Delete > Delete Sheet Rows
All blank rows are removed
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