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.

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 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