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, r As Long

LastRow = ActiveSheet.UsedRange.Rows.Count

LastRow = LastRow + ActiveSheet.UsedRange.Row - 1

Application.ScreenUpdating = False

If WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete Next r 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.

Was this article helpful?

0 0

Post a comment