Insert X rows every Nth row in Excel 2007

[bmacbmac]

Hi. I have a column with about 90,000 rows. Is there a way I can insert 4 blank rows after every row?

I am trying to do this.. Currently have ColumA:
12345
12346
12347
12348
12349

I have a four line batch file I need to write for each instance. In column B i am concatenating data from column a, but need it in 4 individual rows:

ColA ColB
12345 copy 12345 c:\temp
cd temp
rename 12345 12345.tif
copy 12345.tif c:\final
cd..
12346 copy 12346 c:\temp
cd temp
rename 12346 12346.tif
copy 12346.tif c:\final
cd..
12347 copy 12347 c:\temp
cd temp
rename 12347 12347.tif
copy 12347.tif c:\final
cd..
12348 copy 12348 c:\temp
cd temp
rename 12348 12348.tif
copy 12348.tif c:\final
cd..


etc.

Column B isn't my exact script, but just an example for simplicity.

 

[ck1999]

this is a possibility

Sub test()
lastrow = Range("a1").End(xlDown).Row
For counter = lastrow To 2 Step -1
    Rows(counter & ":" & counter + 3).Insert shift:=xlDown
    Range("b" & counter - 1) = "copy " & Range("a" & counter - 1) & " c:\temp"
    Range("B" & counter) = "cd temp"
    Range("b" & counter + 1) = "rename " & Range("a" & counter - 1) & " " & Range("a" & counter - 1) & ".tif"
    Range("b" & counter + 2) = "copy " & Range("a" & counter - 1) & ".tif c:\final"
    Range("b" & counter + 3) = "cd.."
    
Next counter
End Sub

ck1999

 

[ck1999]

I know this is not the vba forum but This is a way to get what you are asking. I am not sure how to do it without using vba.

ck1999

 

[bmacbmac]

Hi there. I apologize but I'm not sure how to do any vba/excel integration. Where do I go in Excel 2007 to try this?

 

[ck1999]

You have to have the developer ribbon open. If you do not click on the circle buttong and choose excel options. It is somewhere in there to select display developer ribbon or something like that. Then under this ribbon choose visual basic editor (I think not sure since I do not have 2007 on this computer). Once you open the vba editor choose insert module and paste my code into it.

In office 03 you can press alt+f11. I am not sure if this will work in office 07 or not.

Once you have the code in the module run the code.

ck1999

 

[bmacbmac]

That works awesome. I actually had to concatenate more fields than I thought and couldn't figure it out. So I just took this from your script:

Sub test()
lastrow = Range("a1").End(xlDown).Row
For counter = lastrow To 2 Step -1
    Rows(counter & ":" & counter + 3).Insert shift:=xlDown
   
    
Next counter
End Sub

So, this just inserts the blank rows for me. I can easily copy/paste the necessary lines throughout the range.

Inserting the blank rows is working great, although it's pretty slow for 90,000 rows.

Thanks for your help!