How to: Remove all blank rows at once

If you have a lot of data it can be difficult to identify and remove the empty rows. There is no way in standard Excel to quickly remove all empty rows* so it can be a time-consuming job. You need to use a macro for that.

Empty rows in your table can cause problems with sorting and filtering. Many people just click on the sorting or filter button and rely in Excel selecting the data range. But when there is an empty row, Excel's "auto detect" assumes that this is the end of your table/data range (which most of the times is the case). If you want to do the sorting of filtering on a table with empty rows, you first have to manually select the entire data range and then do the sorting or filtering.

You can use ASAP Utilities to remove all blank/empty rows in in your worksheet.
The following utility will remove all rows that contain no data within the used range of your worksheet:
ASAP Utilities » Columns & Rows » Remove all empty rows

If you want to remove the blank rows or columns withing your selected range you can use the following utility:
ASAP Utilities » Columns & Rows » Conditional row and column select, hide or delete...
and then choose the option to select, hide or remove the completely empty rows or columns.

Some people emailed me because they expected this utility to reduce the amount of available rows in the worksheet, but it doesn't work like that. The utility only removes the empty rows between your data. It will not reduce the amount of available rows in your worksheets (65536 rows in Excel prior to version 2007 and 1048576 row in Excel 2007).
If a row is removed, a new row is automatically added by Excel at the bottom of your worksheet. That is the normal way Excel works.

*You can select the entire sheet and sort the data to shift the empty rows to the bottom, but that will also change the order of all data.

If you want to do this with a macro yourself, you could use a macro such as the following:

Sub RemoveEmpyRows()
' this macro will remove all rows that contain no data
Dim i             As Long
Dim lLastRow      As Long
lLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Application.ScreenUpdating = False
For i = lLastRow To 1 Step -1
  If WorksheetFunction.CountA(ActiveSheet.Rows(i)) = 0 Then
   ActiveSheet.Rows(i).EntireRow.Delete
  End If
Next i
Application.ScreenUpdating = True
End Sub

Comments are closed.

Pinterest