Starting a loop on row 3, help.

[MurraySMTH]

Hello all. I am using a loop code to add 7 rows in between rows.

The problem is I don't know how to account for the headers. Can someone take a look at my code. I want 7 rows in between each row that has data, but not in between the header and the first line of rows. So the blank rows would start at 3.

Sub AddRows()
    ScreenUpdating = False

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    Dim AddRows As Integer: AddRows = 7

    Dim i As Integer: i = LastRow

    Do While i <> 1
        Rows(i & ":" & i + AddRows - 1).Insert
        i = i - 1
    Loop

    ScreenUpdating = True
End Sub

 

[jasonb75]

As far as I can see, you just need to change this line
Do While i <> [B]3[/B]
Or a slightly different method which might be a little easier to understand.

Sub AddRows()
    ScreenUpdating = False

    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    Dim AddRows As Long: AddRows = 7

    Dim i As Long

    For i = lastrow To 3 Step -1
        Rows(i).Resize(AddRows).Insert
    Next

    ScreenUpdating = True
End Sub

Note that I've used Long instead of Integer, with large volumes of data, Integer can potentially result in runtime errors.

 

[Fluff]

Replace this

    Dim i As Integer: i = LastRow

    Do While i <> 1
        Rows(i & ":" & i + AddRows - 1).Insert
        i = i - 1
    Loop

with

   Dim i As Long
   For i = lastRow To 3
      Rows(i).Resize(addrows).Insert
   Next i

 

[MurraySMTH]

Thank you Jason and Fluff. That is it. Yes you made it way simpler. I needed to change Dim i as Long... good call.

 

[Fluff]

Glad we could help & thanks for the feedback.