Insert new rows with increments from existing rows...

[joeallenfm]

Hi MrExcel board, first time poster in need...

I have 600k rows of data like this;

CODE	Product	Quantity	CustomerID
WMB0000025	Medium Thing	25	x35904930
WMX0000240	Small Thing	25	x49509600
WMB0000391	Medium Thing	25	x39409510

I would like to run a script to insert rows after each existing row where:

1. The number of rows inserted is taken from the quantity cell of the row triggering the insert minus 1.
2. The Code of the inserted rows is the same as the code cell from the triggering row + 1 added the the numerical part of the code... e.g. the first two rows after the first example above would be WMB0000026 and WMB0000027.
3. The Product and Customer ID are the same as the cells from the triggering row.
4. The rows are inserted and do not overwrite any existing rows - so in example above the second row of data (WMX0000240) would still be there after the script had executed the first row of data's insert.

 

[Legacy 143009]

Are your codes always like 3 characters [A-Z] and 7 characters [0-9]?

And what about the quantitiy row? Is it gonna stay the same also?

 

[joeallenfm]

Ah, sorry I should have used a real code.

They are always four characters [A-Z] and 9 charachters [0-9] Here's a real one:

RZMa000406717

 

[Legacy 143009]

And what about the quantitiy row? Is it gonna stay the same also?

?

 

[joeallenfm]

?

Yes, it can stay the same.

 

[Legacy 143009]

I hope it will be fast enough for 600k rows

Sub test()
  Dim lRow As Long, r As Long
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
  Application.ScreenUpdating = False
  For i = lRow To 2 Step -1
    r = Cells(i, 3).Value
    Rows(i + 1).Resize(r).EntireRow.Insert
    With Range("A" & i & ":B" & i)
    .AutoFill Destination:=.Resize(r + 1)
    End With
    Range("C" & i + 1 & ":D" & i + 1).Resize(r).Value = Range("C" & i & ":D" & i).Value
  Next
  Application.ScreenUpdating = True
End Sub

 

[joeallenfm]

That is amazing. Thank you. I just ran on a test range. The only issue was it added 25 rows (e.g. the quantity field) but I wanted 25 minus 1. I've tried to work out where the code needs updating...but failed miserably! If you could help with that, thanks again.

 

[HaHoBe]

Hi Flasbond,

what about the number of rows available in an Excel sheet as 600K rows + 1200 new rows is a lot more than 2^20 rows available

Holger

 

[Legacy 143009]

This should work You don't have to send me anything. Just send your best hopes for my country

Sub test()
  Dim lRow As Long, r As Long
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
  Application.ScreenUpdating = False
  For i = lRow To 2 Step -1
    r = Cells(i, 3).Value - 1
    Rows(i + 1).Resize(r).EntireRow.Insert
    With Range("A" & i & ":B" & i)
    .AutoFill Destination:=.Resize(r + 1)
    End With
    Range("C" & i + 1 & ":D" & i + 1).Resize(r).Value = Range("C" & i & ":D" & i).Value
  Next
  Application.ScreenUpdating = True
End Sub

 

[Legacy 143009]

Hi Flasbond,

what about the number of rows available in an Excel sheet as 600K rows + 1200 new rows is a lot more than 2^20 rows available

Holger

That's a very good point. It will be at least 15.000.000 additional rows if the quantities are 25 in average.
@joeallenfm you are already close to the row limit.