How to repeat rows by x number of times

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

2 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 29, 2010 at 08:42 AM
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
9
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?
0
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?
5
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
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
0
how do I run commands like that? i.e. VBScript?
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
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
0
Thanks
0