how to repeat rows by x number of times Solved/Closed

Question

Hi 

Does anybody know how I can repeat rows in a spreadsheet (or into another spreadsheet) by n number of times specified in the cell in that row

ie 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?

Any help would be greatly appreciated.

Configuration: Windows XP / Internet Explorer 8.0

rizvisa1 · Accepted Answer

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

Angel · Answer

Hello,
THis is very close to what I need, with the only difference that I need to repeat the rows startinf from row10 and columns all the way to S, also I would like to auto number the columns starting at row10 being 1, 11 will be 2 etc, and all the columns up to S will keep the formulas.
What would be the formula for it?

How to repeat rows by x number of times

2 responses

Similar discussions

Subscribe To Our Newsletter!