Rows in an Excel worksheet can be repeated a specified number of times with the help of a user-defined function. A
user defined function can be written to
repeat rows a specified number of times. Some knowledge of programming concepts such as if-else conditions and do while loops may be necessary to write such a function. The
Selection.insert command can be used to insert rows in the worksheet of Excel Office software. There is no need to write the code from scratch as code snippets are freely available for use in worksheets. However, one must ensure that the user-defined function or subroutine to
repeat rows a specified number of times is inserted in the right place.
[Excel] Repeat rows a specified number of times
Issue
Does anybody know how I can repeat rows in a spreadsheet (or into another spreadsheet) by a certain number of times specified in the cell in that row?
i.e. from this table:
Column A Column B
Peter 3
James 7
David 4
I need to produce this table:
Column A Column B
Peter 3
Peter 3
Peter 3
James 7
James 7
James 7
James 7
James 7
James 7
James 7
David 4
David 4
David 4
David 4
Any ideas on a simple way of doing this?
Solution
Try this:
Sub CopyData()
Dim lRow As Long
Dim RepeatFactor As Variant
lRow = 1
Do While (Cells(lRow, "A") <> "")
RepeatFactor = Cells(lRow, "B")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then
Range(Cells(lRow, "A"), Cells(lRow, "B")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "B")).Select
Selection.Insert Shift:=xlDown
lRow = lRow + RepeatFactor - 1
End If
lRow = lRow + 1
Loop
End Sub
Note
Thanks to
rizvisa1 for this tip on the forum.
See also
Knowledge communities.
