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.

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



--


Gary


"quartz" <[email protected]> wrote in message
news:[email protected]...
>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.

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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>

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

--
Regards,
Tom Ogilvy


"quartz" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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.
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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.
> > >
> > >
> > >
>
>
>