Insert Rows

kb22549 · Aug 3rd 2004

I'm sure someone can help, I would like a macro to select every other row in a spreadsheet, and insert a row ahead of them. Any help would be greatly appreciated. I do not know a lot of VBA, but I'm trying to learn.

jindon · Aug 4th 2004

Hi,

try the code

Code

Sub Insert_Rows()
Dim i As Long, s, e
s = InputBox("Starting Row")
e = InputBox("Ending Row" & Chr(10) & Chr(10) & _
"must be equal or greater than Starting Row")
Application.ScreenUpdating = False
If (s = "") + (e = "") + (s > e) Then Exit Sub
    If (IsNumeric(s) = False) + (IsNumeric(e) = False) Then Exit Sub
        For i = s To e * 2 - s Step 2
            Rows(i).Insert
        Next
Application.ScreenUpdating = True
End Sub

Display More

dangelor · Aug 4th 2004

Hello kb

Select the range you wish to expand and run this:

Code

Sub AddRowsOdd()
    Dim iRow As Integer
    For iRow = 1 To Selection.Rows.Count * 2
        If iRow Mod 2 = 1 Then
            Selection.Rows(iRow).Rows.Insert
        End If
    Next iRow
End Sub

Anonymous · Aug 4th 2004

Here's a slightly different approach (actually two, because I wasn't sure if you wanted the final result to have alternating blank rows, or every third row blank.) The first produces alternating blank rows. The second every third row is blank.

Code

Sub InsertRows()
Dim Lastrow As Long, r As Long
Lastrow = Range("A65536").End(xlUp).Row
For r = Lastrow To 2 Step -1
Rows(r).Insert
Next r
End Sub


Sub InsertRows2()
Dim Lastrow As Long, r As Long
Lastrow = Range("A65536").End(xlUp).Row
For r = Lastrow - 1 To 2 Step -2
Rows(r).Insert
Next r
End Sub

Display More

Insert Rows

Participate now!

Share