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
– Histerical, Commented May 20, 2016 at 21:27
@Histerical - Ahh, music to my ears :) (...err, eyes I suppose.) — BruceWayne
– BruceWayne, Commented May 20, 2016 at 21:28
Thank you very much for both proposed solutions, they work perfectly. Much appreciated! Happy weekend to everybody — Eliseo Sempere
– Eliseo Sempere, Commented May 21, 2016 at 6:57

Gary's Student · Accepted Answer · 2016-05-20 21:57:47Z

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, 2016 at 21:57

answered May 20, 2016 at 21:27

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BruceWayne Over a year ago

I also recommend not using .Select. 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.

Gary's Student Over a year ago

@BruceWayne I completely agree with you ! ......................I am really more concerned with the exact spacing of the added rows.

Gary's Student Over a year ago

@BruceWayne Suggestion adopted !!

Collectives™ on Stack Overflow

Sequential row selection in VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related