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.

Related:

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
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?
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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