Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
by the RAND function). The winner will be determined by sorting the data based on column 3 (the
highest random number wins).
Figure 11-9: The goal is to duplicate rows based on the value in column B.
The goal is to duplicate the rows so that each person will have a row for each ticket purchased.
For example, Barbara purchased two tickets, so she should have two rows. The procedure to
insert the new rows is shown here:
Dim cell As Range
‘ 1st cell with number of tickets
Set cell = Range(“B2”)
Do While Not IsEmpty(cell)
If cell > 1 Then
Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, _
Range(cell, cell.Offset(cell.Value - 1, 1)).EntireRow.FillDown
Set cell = cell.Offset(cell.Value, 0)
The cell object variable is initialized to cell B2, the first cell that has a number. The loop inserts
new rows and then copies the row using the FillDown method. The cell variable is
incremented to the next person, and the loop continues until an empty cell is encountered. Figure
11-10 shows the worksheet after running this procedure.
A workbook that contains this example is available on the companion CD-ROM. The file
is named duplicate rows.xlsm .