## 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 by the RAND function). The winner will be determined by sorting the data based on column 3 (the highest random number wins).

 A B C D £ 1 Mam« Mo. Tickets Hind oin 2 Alan 1 0,75245004 3 Barbara 0.72935607 4 Cnartie 1 0.41277549 S Dave 5 C.G62&1271 6 Frank 0,34540676 7 Gil da 1 0,8052792 £ Huben 1 0.97467075 9 Inez 2 0.62376696 10 Mart: 1 0.64050016 11 Ma rati 10 0.70054717 12 Penelope 2 0.6744104J 13 fiance 1 0 3963156 14 Wendy 2 0,25557331 15 h * M Sheetl m* , r HHJ

Figure 11-9: The goal is to duplicate rows based on the value in column B.

Figure 11-9: The goal is to duplicate rows based on the value in column B.

The goal is to duplicate the rows so 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:

Sub DupeRows()

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

0)).EntireRow.Insert Range(cell, cell.Offset(cell.Value - 1, 1)) . EntireRow.FillDown End If

Set cell = cell.Offset(cell.Value, 0) Loop End Sub

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 B C D t 1 Name No. Ticket Random 2 Alan i 0.20078745 3 Barbara 2 0&12561S 4 Barbara 1 0.9S978267 5 Charlie 1 0.9096D5S1 6 Dave S 0.2255792 7 Dave 5 0.49545822 = 8 Dave S 0.4661790S 9 Dave 5 0.473035 &S 10 Dave 5 0.86463447 :: Frank 3 0.52724143 12 Frank 3 0.1224S409 13 Frank 3 0-117Ü3Q4 14 Gilda 1 0.B736246 15 Hubert 1 0.73547&S 16 Inez 2 0-06544751 17 Inez 2 0.4B685292 IS Mark 1 0-J23002J7 :■' Norah 10 0-2347447 20 Norah 10 0-9S165&66 21 Norah 10 0-6409 719 9 H ^ ► h Sheeti ^ IP : ► m

Figure 11-10: New rows were added, according to the value in column B. CD-ROM

Figure 11-10: New rows were added, according to the value in column B. CD-ROM

A workbook that contains this example is available on the companion CD-ROM. The file is named %

duplicate rows.xlsm .

0 0