How to repeat rows by x number of times [Solved/Closed]

- - Latest reply:  Riteon - Jun 9, 2015 at 08:54 AM
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.

See more 

2 replies

Best answer
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
9
Thank you
Try this

S
ub 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

Thank you, rizvisa1 9

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2617 users this month

Is it possible to have this VBA so that you put the number within the formula itself, rather than basing on the value of B. e.g. I want all of them to be copied 10 times?
3
Thank you
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?
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
this tells the start point for copying. you can change to suit your requirement
lRow = 1
In the example, it is saying copy from row 1

this line copies the row
Range(Cells(lRow, "A"), Cells(lRow, "B")).Copy
change to suit your req. here, it is copying column A and B

most easy way for number would be let the copy process complete and then loop again for each row and put the number and move to next line
how do I run commands like that? i.e. VBScript?
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Not vbsrcipt. You need to run it as an excel macro
1. open workbook
2. press ALT + F11 at same time to open VB Editor
4. Click on insert and add a new module.
5. paste code
6. run the macro by pressing F5 or choosing from Excel menu tool, macro, run