Insert two rows in one line of code?

[Guest]

I am using Office 2003 on Windows XP.

Presently I have code that inserts two blank rows based on changes in a
criteria column, the insert code (which feeds the appropriate row number from
an array) looks like this:

For lX = UBound(saRows) To 1 Step -1
Rows(saRows(lX)).EntireRow.Insert Shift:=xlDown
Rows(saRows(lX)).EntireRow.Insert Shift:=xlDown
Next lX

The above code is inefficient since it has to perform two separate inserts
every time. Rather than having two separate identical code lines and
therefore doing two separate inserts, isn't there a way to tell it how many
rows to insert and then insert them all in one action and in one line of
code? I'm trying to speed up the insert process.

Could someone please fix my code to do a two line insert in one go?

Thanks much in advance.

 

[Gary Keramidas]

try this
For lX = UBound(saRows) To 1 Step -1
Rows(saRows(lX))..EntireRow.Resize(2).Insert
Next lX

 

[Guest]

Thanks Gary.

FYI, this method reduced 5,334 row inserts from 4:00 minutes, to 2:05!
Thanks!

 

[Tom Ogilvy]

did you also try setting calculation to manual before running the improved
code?

 

[Guest]

Yes, thanks Tom for that thought, my code includes the following to help
things out:

Application.ScreenUpdating = False
ActiveSheet.DisplayAutomaticPageBreaks = False
Application.Calculation = xlCalculationManual

Thanks.