URGENT!!!!!!: Macro to Insert Multiple Blank Rows

Posted by Gregory K. Barton on September 20, 2001 5:54 AM

In column A I have values in Cells A2 through A1821. I need to insert 16 blank rows between each value with an automated macro that will do it all at once. Any immediate help would be greatly appreciated. Thanks.

Posted by Henry Root on September 20, 2001 6:35 AM

Try this (not tested). I think it will take quite a long time to run, so I've included a status bar message so the progress can be seen :-

Dim x As Integer
Application.ScreenUpdating = False
For x = 1821 To 2 Step -1
Application.StatusBar = "Inserting rows. Row " & x & " of 1821 "
Cells(x, 1).EntireRow.Resize(16).Insert
Next
Application.StatusBar = False

Posted by Rob Jackson on September 20, 2001 6:39 AM

Here you go...

Sub Inserter()
For Rowloop = 1821 To 2 Step -1
Rows(Rowloop & ":" & (Rowloop + 15)).Insert Shift:=xlDown
Next Rowloop
End Sub

This will insert 16 blank rows between each line

Posted by Rob Jackson on September 20, 2001 6:44 AM

Nice!

I had never come across the resize thing.

Nice...

Posted by Gregory K. Barton on September 20, 2001 6:59 AM

Thanks to Rob and Henry! You have just saved me hours of work due to my lack of knowledge of VB and VBA. You guys know your stuff!