Instert Rows Based on Cell Value

Hello,

I could really use a hand on this one.

I have multiple lines of different products ordered by different people. In each row in excel it shows the product that was ordered as 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 macro 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 a quantity of 1

can anyone help me out with that?

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

derekrap wrote:
>
> Hello,
>
> I could really use a hand on this one.
>
> I have multiple lines of different products ordered by different
> people. In each row in excel it shows the product that was ordered as
> 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 macro
> 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 a
> quantity of 1
>
> can anyone help me out with that?
>
> --
> derekrap
> ------------------------------------------------------------------------
> derekrap's Profile: http://www.excelforum.com/member.php...o&userid=20656
> View this thread: http://www.excelforum.com/showthread...hreadid=481013

--

Dave Peterson