How to duplicate rows x number of times

Posts
3
Registration date
Tuesday July 9, 2019
Status
Member
Last seen
July 9, 2019
- - Latest reply: ac3mark
Posts
12506
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
July 10, 2019
- Jul 9, 2019 at 04:50 PM
Does anybody know how I can repeat rows in a spreadsheet by n number of times specified in cell B in that row

ie from this table:

Column A, Column B, Columns C-Z have other data that needs to be duplicated to each row too
Jack, 2, Data, Data, Data, etc...
Mary, 4, Data, Data, Data, etc...
Henry, 20, Data, Data, Data, etc...
Ross, 3, Data, Data, Data, etc...

I need it to look like this:

Column A, Column B, Columns C-Z
Jack, 2, Data, Data, Data, etc...
Jack, 2, Data, Data, Data, etc...
Mary, 4, Data, Data, Data, etc...
Mary, 4, Data, Data, Data, etc...
Mary, 4, Data, Data, Data, etc...
Mary, 4, Data, Data, Data, etc...

and so on. Thanks!
See more 

1 reply

Posts
12506
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
July 10, 2019
1131
0
Thank you
Do you know what a for loop is?
carlos.guerrero18
Posts
3
Registration date
Tuesday July 9, 2019
Status
Member
Last seen
July 9, 2019
-
I do not, unfortunately.
carlos.guerrero18
Posts
3
Registration date
Tuesday July 9, 2019
Status
Member
Last seen
July 9, 2019
-
I tried running this:
Sub CopyData()
Dim xRow As Long
Dim VInSertNum As Variant
xRow = 1
Application.ScreenUpdating = False
Do While (Cells(xRow, "A") <> "")
VInSertNum = Cells(xRow, "B")
If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
Range(Cells(xRow, "A"), Cells(xRow, "Z")).Copy
Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "Z")).Select
Selection.Insert Shift:=xlDown
xRow = xRow + VInSertNum - 1
End If
xRow = xRow + 1
Loop
Application.ScreenUpdating = False
End Sub

But I'm getting a bug in the "If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then" line. Any idea?
ac3mark
Posts
12506
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
July 10, 2019
1131 -
Place a msgbox to display the variable of VInSertNum, as in :
msgbox("the variable is " & VInSertNum)

BTW, is there a numeric value in cell(xRow,"B")?

You may have to put Thisworkbook.worksheets("yoursheetname").Cells(xRow,"B") in the variable initialization of:

VInSertNum = Thisworkbook.WorkSheets.("yoursheetname").Cells(xRow, "B").value

Give it try!
Respond to ac3mark