Copy row and insert based on value in column

smittyxsmith - Jan 25, 2022 at 12:43 PM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Feb 7, 2022 at 12:03 PM
I have never used macros before! I have a spreadsheet where I need to duplicate rows based upon a quantity in a column. For example, Row 1 has a value of 4 in Column J so I need to duplicate Row 1 four times. Whereas, Row 2 has a value of 7 in Column J so I need to duplicate Row 2 seven times. Bonus points if I can can choose which columns data to duplicate.

Figuring this out will save me HOURS UPON HOURS of work. I am completely lost! Help!


System Configuration: Macintosh / Chrome 97.0.4692.71

1 reply

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Updated on Feb 7, 2022 at 12:04 PM
Hi Smittyxsmith,

Here is your macro code:
Sub RunMe()
Dim x, y, lRow As Integer

lRow = Range("J" & Rows.Count).End(xlUp).Row

For x = lRow To 1 Step -1
    y = Cells(x, "J")
    Do
        'For the bonus points adjust the column letters A and J to determine the columns to be copied.
        With Range(Cells(x, "A"), Cells(x, "J"))
            .Copy
            .Insert Shift:=xlDown
        End With
        y = y - 1
    Loop Until y = 0
Next x
End Sub


Best regards,
Trowa
0