Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to put series name next to individual line in line grap | Charts and Charting in Excel | |||
Editing code to insert two rows | Excel Programming | |||
Code to delete a Line in a another code | Excel Programming | |||
Continue line of VBA code on the next line | Excel Programming | |||
Help with some code to insert rows | Excel Programming |