Sequential row selection in VBA

Question

I am both new to the forum and to VBA (for excel).

I am trying to do a very simple thing:

• I have a dataset of 1500 lines--> 150 companies each having yearly observations for 10 years.
• I need to add one more line (i.e. one more year) at the beginning of each subset.

So to avoid typing ALT+H+I+R 150 times (and also because I´ll probably encounter the same issue in the future) I am trying out VBA.

This is the code I have so far:

Sub InsertRows()
' InsertRows Macro

Dim Var As Integer
Var = 5
Do While Var < 1700
    Var = Var + 10
    Range("F" & Var).Select
    Selection.EntireRow.Insert
Loop

End Sub

The problem I have is that the program "falls behind" by one Row for each repetition given that a new row is added with each loop.

I would greatly appreciate your input!

Thx! Eliseo

Answer 1

Does this get you any closer:

Sub InsertRows()
    Dim Var As Long
    Var = 1700
    Do While Var > 10
        Var = Var - 10
        Range("F" & Var).EntireRow.Insert
    Loop
End Sub

You may have to change to starting row to get the desired spacing