Insert two rows in one line of code?
 

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.

Insert two rows in one line of code?
 

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



--


Gary


"quartz" wrote in message
...
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.

Insert two rows in one line of code?
 

Thanks Gary.

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

"Gary Keramidas" wrote:

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



--


Gary


"quartz" wrote in message
...
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.

Insert two rows in one line of code?
 

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

--
Regards,
Tom Ogilvy


"quartz" wrote in message
...
Thanks Gary.

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

"Gary Keramidas" wrote:

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



--


Gary


"quartz" wrote in message
...
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.

Insert two rows in one line of code?
 

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"quartz" wrote in message
...
Thanks Gary.

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

"Gary Keramidas" wrote:

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



--


Gary


"quartz" wrote in message
...
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.