How to duplicate rows x number of times

Report
Posts
3
Registration date
Tuesday July 9, 2019
Status
Member
Last seen
July 9, 2019
-
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
-
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!

5 replies

Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1575
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
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1575
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!