Instert Rows Based on Cell Value

[derekrap]

Hello,

I could really use a hand on this one.

I have multiple lines of different products ordered by differen
people. In each row in excel it shows the product that was ordered a
well as the quantity and some other information.

What I need to do is expand each row based on the quantity value.
Essentially if someone ordered 4 of a particular product i want a macr
to add three rows below it.

for i.e.

this:

Invoice # / Date / Quantity/
2532434 / 11/02/ 5

would turn into this:

2532434 / 11/02 / 1
2532434 / 11/02 / 1
2532434 / 11/02 / 1
2532434 / 11/02 / 1
2532434 / 11/02 / 1

so the macro will add 4 rows below the 1st to make a total of 5 rows

I essentially want to expand the quantity sold so each row shows
quantity of 1

can anyone help me out with that

 

[Dave Peterson]

Try this against a copy of your data--it destroys the original data when it
builds the new stuff.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim RowsToInsert As Long

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
RowsToInsert = .Cells(iRow, "C").Value - 1
If RowsToInsert = 0 Then
'do nothing
Else
.Rows(iRow + 1).Resize(RowsToInsert).Insert
.Cells(iRow + 1, "A").Resize(RowsToInsert, 2).Value _
= .Cells(iRow, "A").Resize(1, 2).Value
.Cells(iRow, "C").Resize(RowsToInsert + 1, 1).Value = 1
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm