Insert a specific number of rows

[Faraz A. Qureshi]

What would be the correct syntax of inserting a specific number of rows below
the active cell? For instance 4 or 5 rows?

 

[OssieMac]

Hi Faraz,

Try the following. Note that a space and underscore at the end of a line is
a line break in an otherwise single line of code.

Range(ActiveCell.Offset(1, 0), _
ActiveCell.Offset(5, 0)) _
.EntireRow.Insert

 

[Faraz A. Qureshi]

OK!
So there is no direct way and I shall be required to use Offset().

 

[Rick Rothstein]

This is a little more direct..

ActiveCell.Offset(1).Resize(4).EntireRow.Insert

You seem hesitant about using Offset for some reason, although I'm not sure
why. You can do it without using Offset like this...

Cells(ActiveCell.Row + 1, 1).Resize(4).EntireRow.Insert

but, quite frankly, I would just use the first method and be done with it.

 

[OssieMac]

See the following site for a more complex method of inserting rows and
keeping formulas. I have not tested it but you might find it interesting.

http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[Faraz A. Qureshi]

Thanx Rick!
It's not actually that I am hesitant. I just was eager 2 know if there could
be direct way for the purpose of knowledge.

 

[Rick Rothstein]

See, this is what happens when you post a message at 3:00 am in the morning
(local time), you get sloppy. That last example I posted works, but using
Cells is not the way I should have done it, rather, I should have used Rows
directly...

Rows(ActiveCell.Row + 1).Resize(4).Insert

Because this uses one less function call than either of my prior examples,
this would be my preference for use in actual code.