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
