Deleting all empty rows

The following procedure deletes all empty rows in the active worksheet. This routine is fast and efficient because it doesn't check all rows. It checks only the rows in the used range, which is determined by using the UsedRange property of the Worksheet object.

Sub DeleteEmptyRows()

Dim LastRow As Long

Dim r As Long

Dim Counter As Long

Application.ScreenUpdating = False

LastRow = ActiveSheet.UsedRange.Rows.Count + _

ActiveSheet.UsedRange.Rows(1).Row - 1 For r = LastRow To 1 Step -1

If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete Counter = Counter + 1

End If Next r

Application.ScreenUpdating = True MsgBox Counter & " empty rows were deleted." End Sub

The first step is to determine the last used row and then assign this row number to the LastRow variable. This is not as simple as you might think because the used range may or may not begin in row 1. Therefore, LastRow is calculated by determining the number of rows in the used range, adding the first row number in the used range, and subtracting 1.

The procedure uses Excel's COUNTA worksheet function to determine whether a row is empty. If this function returns 0 for a particular row, the row is empty. Notice that the procedure works on the rows from bottom to top and also uses a negative step value in the For-Next loop. This is necessary because deleting rows causes all subsequent rows to move up in the worksheet. If the looping occurred from top to bottom, the counter within the loop would not be accurate after a row is deleted.

The macro uses another variable, Counter , to keep track of how many rows were deleted. This number is displayed in a message box when the procedure ends.


A workbook that contains this example is available on the companion CD-ROM in a file named delete empty rows.xlsm .

0 0


Post a comment