HUGE Inventory Assistance - duplicate a row based on a cell value or quantity [Solved]

Question

HUGE Inventory Assistance - duplicate a row based on a cell value or quantity [Solved]

Report

klewis24

Posts: 2
Registration date: Thursday October 1, 2020
Status: Member
Last seen: November 9, 2020

- Oct 1, 2020 at 01:46 PM
klewis24

Posts: 2
Registration date: Thursday October 1, 2020
Status: Member
Last seen: November 9, 2020

- Nov 9, 2020 at 11:50 PM

I have an inventory list that will end up being ~60k rows of data based on the sum of Col1.

tab name - "inventory"

Col1 - original quantity
Col2 - control number (similar to a SKU)
Col3 - category
Col4 - description of item

to start there is 3064 rows which have quantities that range from 1 to 1001

If possible, i'd need a VB code or macro that will take Row2 and insert enough rows below to satisfy the quantity in Col1 Row2 but whilst doing so, copying the data in Col2, Col3 and Col4 in to the new rows

IF anyone could help, I'd be very grateful and if needed I'll venmo/cashapp you $15

HUGE Inventory Assistance - duplicate a row based on a cell value or quantity
Excel duplicate rows based on cell value ✓ - Forum - Excel
Insert rows based on cell value - Forum - Excel
Excel vba insert multiple rows based on cell value - How-To - Excel
Code to move a row based on fix values and paste it ✓ - Forum - Excel
Excel copy row based on cell value ✓ - Forum - Excel

2 replies

Answer 2 / 2

klewis24

Posts: 2
Registration date: Thursday October 1, 2020
Status: Member
Last seen: November 9, 2020

Nov 9, 2020 at 11:50 PM

Thank you very much for this resolution.

Reply Ask a question

TrowaD · Accepted Answer · 2020-10-08T11:16:02-0400

Answer 1 / 2

TrowaD

Posts: 2686
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: January 5, 2021

452
Oct 8, 2020 at 11:16 AM

Hi Klewis,

Here is a code to perform the requested task:

Sub RunMe()
Dim mQ, y As Integer, x As Long
Application.ScreenUpdating = False

x = 2

Do
    mQ = Range("A" & x).Value - 1
    If mQ <> 0 Then
        For y = 1 To mQ
            Rows(x).Copy
            Rows(x + 1).Insert
        Next y
        Range(Cells(x + 1, "A"), Cells(x + mQ, "A")).ClearContents
    End If
    x = x + mQ + 1
Loop Until Range("A" & x).Value = vbNullString
Application.ScreenUpdating = True
End Sub

Best regards,
Trowa

HUGE Inventory Assistance - duplicate a row based on a cell value or quantity [Solved]

2 replies

Your reply

Subscribe To Our Newsletter!