MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Insert rows by macros/Formulas


Posted by Mo on October 02, 2001 1:34 PM

Hello everyone,
I have a spreadsheet of a few hundred rows. What I want to do is insert 6 blank rows after every row. As you appreciate such a task will take quite long if I do it manually. Formulas or macros appreciated.
Mo


Posted by Barrie Davidson on October 02, 2001 1:45 PM

Hi Mo, try this VBA code.

Sub Insert_6_rows()
' Written by Barrie Davidson
Range("A1").Select
Do Until Selection.Value = ""
ActiveCell.Offset(1, 0).Range("A1:A6").Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(6, 0).Select
Loop
Range("A1").Select
End Sub


Regards,
BarrieBarrie Davidson

Posted by Barrie Davidson on October 02, 2001 1:54 PM

An improvement to my answer

Mo, this code will allow you to specify the number of rows to insert (more versatile).

Sub Insert_rows()
' Written by Barrie Davidson
Dim insertNumber As Integer
On Error Resume Next
Range("A1").Select
insertNumber = CInt(InputBox("Enter number of rows to insert"))
If insertNumber <= 0 Then
MsgBox ("Invalid Number Entered")
Exit Sub
End If
Do Until Selection.Value = ""
ActiveCell.Offset(1, 0).Range("A1:A" & insertNumber).Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(insertNumber, 0).Select
Loop
Range("A1").Select
End Sub


Regards,
BarrieBarrie Davidson

Posted by Mo on October 02, 2001 4:00 PM

Thanks Barry