Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
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.
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
Counter = Counter + 1
Application.ScreenUpdating = True
MsgBox Counter & “ empty rows were deleted.”
The first step is to determine the last used row and then assign this row number to the LastRow
variable. This calculation isn’t 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
negative step value 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 wouldn’t
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 .
Duplicating rows a variable number of times
The example in this section demonstrates how to use VBA to create duplicates of a row. Figure
11-9 shows a worksheet for an office raffle. Column A contains the name, and column B contains
the number of tickets purchased by each person. Column C contains a random number (generated