Inserting a Row with VBA

  • Thread starter Thread starter Thomas M
  • Start date Start date
T

Thomas M

Excel 2000

This is driving me nuts!

All I want to do is insert a row at the current position, then move down
two rows. I recorded a macro, but the resulting macro contains A1-style
references. The macro needs to be dynamic, so it needs to use R1C1-style
references.

I've done this type of thing before, but can't for the life of me
remember how. I tried the online "Help", which was utterly useless. It
appears the Microsoft has done a fine job in totally burying any
informaiton that would actually be useful in solving this problem.

Does anyone know how to do this?

--Tom
 
Excel 2000

This is driving me nuts!

All I want to do is insert a row at the current position, then move down
two rows. I recorded a macro, but the resulting macro contains A1-style
references. The macro needs to be dynamic, so it needs to use R1C1-style
references.

I've done this type of thing before, but can't for the life of me
remember how. I tried the online "Help", which was utterly useless. It
appears the Microsoft has done a fine job in totally burying any
informaiton that would actually be useful in solving this problem.

Does anyone know how to do this?

--Tom
Click to expand...

ActiveCell.EntireRow.Insert
ActiveCell.Offset(2).Select
 
Tom
How about:

Selection.EntireRow.Insert
ActiveCell.Offset(2, 0).Range("A1").Select

Regards
Philip
 
Try something like that.
Sub a()
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
End Sub
 
Auric__ said:
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2).Select
Click to expand...

Thanks for the help! It works great. Now that I see it, I do remember
coming across Offset before, but that was a long time ago. The last time I
tried to do something like this, I used R1C1 references, and it worked, but
I just couldn't get the syntax right this time, and for some reason I
couldn't find it in Help. This method looks better, though.

--Tom
 
Thanks for the help! It works great. Now that I see it, I do remember
coming across Offset before, but that was a long time ago. The last time I
tried to do something like this, I used R1C1 references, and it worked, but
I just couldn't get the syntax right this time, and for some reason I
couldn't find it in Help. This method looks better, though.
Click to expand...

It *is* R1C1. Offset works like this:
Offset ([Rows], [Columns])
So you can move left and right, also - leaving Rows empty - Offset( , 1)
- changes columns without changing rows.
 
Back
Top