Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
2
Registration date
Thursday October 1, 2020
Status
Member
Last seen
November 9, 2020
 -
Posts
2
Registration date
Thursday October 1, 2020
Status
Member
Last seen
November 9, 2020
 -
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

2 replies

Answer 1 / 2
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
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
Comment
2
Thank you
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Answer 2 / 2
Posts
2
Registration date
Thursday October 1, 2020
Status
Member
Last seen
November 9, 2020
Thank you very much for this resolution.
Reply Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!