Macro to insert number of rows based on criteria

[mtampa]

Hi Guys,

I have a workbook that is about 20,000 rows deep. I am looking for a macro that inserts the correct number of rows based on the number of units sold so I can save space and have only the correct of rows for my countries of sale.

For example:

Date Capacity Sold
30-Oct 20 5
31-Oct 20 8

Since I have 5 sold units on 30-Oct, I want the macro to insert 5 rows after that date and 8 after 31-Oct.

I have been searching around for an idea and will continue searching, but if anyone has any ideas, please let me know. I can also email someone a sample dataset if they need.

Thanks,
-Mike

 

[machopicho]

If sold in column C try:

Sub test()
On Error Resume Next
For r = Cells(Rows.Count, "C").End(xlUp).Row To 2 Step -1
  For rw = 2 To Cells(r, "C").Value + 1
    Cells(r + 1, "C").EntireRow.Insert
Next rw, r
End Sub

 

[jim may]

Here's an Example - Create a New workbook and post all this in there, including the code below..

Here's your workbook BEFORE the New Days Info in Cells B2:D2

Excel Workbook
	A	B	C	D	E
1		Date	Capacity	Sold
2	New record	30-Oct	20	5	<< Enter And then Run Macro - InsertNewRecords
3
4
5	Date	Capacity
6	29-Oct	15
7	29-Oct	15
8	29-Oct	15
9	28-Oct	18
10	28-Oct	18
11	28-Oct	18
12	28-Oct	18
Sheet1

Excel 2007

Paste this Macro into a standard module

Sub InsertNewRecords()
NumSold = Range("D2").Value
Range("A6").Select
ActiveCell.Rows("1:" & NumSold).EntireRow.Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Select
Range("A6").Resize(NumSold, 2).Value = Range("B2:C2").Value
End Sub

Now, Run it!!

Your Workbook should look like this...

Excel Workbook
	A	B	C	D	E
1		Date	Capacity	Sold
2	New record				<< Enter And then Run Macro - InsertNewRecords
3
4
5	Date	Capacity
6	30-Oct	20
7	30-Oct	20
8	30-Oct	20
9	30-Oct	20
10	30-Oct	20
11	29-Oct	15
12	29-Oct	15
13	29-Oct	15
14	28-Oct	18
15	28-Oct	18
16	28-Oct	18
17	28-Oct	18
Sheet1

Excel 2007

 

[mtampa]

Thanks guys! I really appreciate it!

 

[mirabeau]

Another idea for you.

Sub insertrow()
Dim e As Range
Set e = Range("C2")
Do
Range(e(2), e(2)(e.Value)).EntireRow.Insert
Set e = e.End(4)
Loop Until e.Row = Rows.Count
End Sub