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

Post a comment