# How to duplicate rows x number of times

Closed
carlos.guerrero18 Posts 3 Registration date Tuesday July 9, 2019 Status Member Last seen July 9, 2019 - Jul 9, 2019 at 03:24 PM
Blocked Profile - 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!

## 1 response

Blocked Profile
Jul 9, 2019 at 03:27 PM
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
Jul 9, 2019 at 03:30 PM
I do not, unfortunately.
carlos.guerrero18 Posts 3 Registration date Tuesday July 9, 2019 Status Member Last seen July 9, 2019
Jul 9, 2019 at 03:40 PM
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?
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!