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

Also I would remove the part where you select and then insert and just put `rows(var).entirerow.insert` — Histerical, May 20 '16 at 21:27
@Histerical - Ahh, music to my ears :) (...err, eyes I suppose.) — BruceWayne, May 20 '16 at 21:28
Thank you very much for both proposed solutions, they work perfectly. Much appreciated! Happy weekend to everybody — Eliseo Sempere, May 21 '16 at 06:57

Gary's Student · Accepted Answer · 2016-05-20T21:57:47.413

3

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

edited May 20 '16 at 21:57

answered May 20 '16 at 21:27

Gary's Student

95,722
10
59
99

3

I also recommend [not using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). I know you're probably just trying to fix OP's issue, while keeping his style, but I think it's simple enough to explain (with his example), and it is certainly best practice. – BruceWayne May 20 '16 at 21:28
1

@BruceWayne I completely agree with you ! ......................I am really more concerned with the exact spacing of the added rows. – Gary's Student May 20 '16 at 21:56
1

@BruceWayne Suggestion adopted !! – Gary's Student May 20 '16 at 21:58

Sequential row selection in VBA

1 Answers1