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

stressedout - Apr 29, 2010 at 04:51 AM - 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 

7 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 29, 2010 at 08:42 AM
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

Something to say? Add comment

CCM has helped 1674 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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 8, 2011 at 02:25 PM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Nov 30, 2011 at 01:35 PM
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