VBA/Excel Formula to insert rows

[RichTom44]

Hello,

Is there a formula in Excel or VBA for inserting rows automatically based on the given qty? I have attached a sample the first column is the Name of the items and the second column is the number of qty so, I need to insert a row based on the number of qty. If the number of qty is 5, I want to insert 4 rows.

 

[Peter_SSs]

Assuming the QTY column is column C and that heading is in row 1, try this macro.

Sub Insert_Rows()
  Dim r As Long, rws As Long
  
  Application.ScreenUpdating = False
  For r = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
    rws = Cells(r, "C").Value
    If rws > 1 Then Rows(r + 1).Resize(rws - 1).Insert
  Next r
  Application.ScreenUpdating = True
End Sub

 

[RichTom44]

Assuming the QTY column is column C and that heading is in row 1, try this macro.

Sub Insert_Rows()
  Dim r As Long, rws As Long
 
  Application.ScreenUpdating = False
  For r = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
    rws = Cells(r, "C").Value
    If rws > 1 Then Rows(r + 1).Resize(rws - 1).Insert
  Next r
  Application.ScreenUpdating = True
End Sub

Hi Sir, you are a lifesaver. Thank you so much It works perfectly.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.