Insert row very slow in Excel 2002

[Guest]

I have VB code that was written for Excel 2000 and does not work properly under Excel 2002. The code inserts rows using rows(rownumber).insert. There are about 200 rows of information below where the row is being inserted. Under Excel 2000, this instruction runs in a blink. Under Excel 2002 is take from 30 to 60 seconds to run. My code creates a report by inserting about 100 rows. So now it takes over 1 1/2 hours to create the report that I used to create in 3 minutes. Does anyone have any ideas why this is so slow and how to correct it?
Thanks

 

[Frank Kabel]

Hi Mike
it would be helpful if you may post the relevant parts of your code.
Some ideas though:
- disbale screenupdating and automatic calculation at the beginning of
your macro (enable it at the end)
- Do you have many complex formulas added in the Excel 2002 workbook

 

[Dave Peterson]

When I'm inserting/deleting rows or columns, I make sure the dotted lines that
show up when you do a print preview aren't visible.

If they're visible, then I think excel wants to determine where they should be
placed after every insert/delete.

Lots of my code starts with lines like:

activesheet.displaypagebreaks = false

But that sounds like a lot of time to make up.

You may want to take a look at these two sites
(Charles Williams and David McRitchie):

http://www.decisionmodels.com
http://www.mvps.org/dmcritchie/excel/slowresp.htm

(Along with trying the stuff Frank suggested)

 

[Guest]

The application builds a project status report. It starts out with a worksheet that consists of one row for each project. There are about 200 rows. It creates the report at the top of the same worksheet by inserting rows. It starts by inserting a header row. Then it moves from row to row, evaluating the information in each data row. When it finds a data row containing a project that qualifies for the report, it cuts the row and inserts a row into the report section pasting the cut information into the inserted report row.

The small section of code below cuts the qualifying data row and inserts the new report row, pasting the information. vRow is the number of the current data row containing the qualifying project information. vInsertRow is the number of the report row where the new row is to be inserted and the information is to be pasted. The insert instruction runs in a blink in Excel 2000 but takes from 30 to 60 seconds in Excel 2002

Dim vRow As Intege
Dim vInsertRow As Intege

Rows(vRow).Cu
Rows(vInsertRow).Insert Shift:=xlDow

 

[Frank Kabel]

Hi Mike
try

Sub foo()
Dim vRow As Integer
Dim vInsertRow As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'...
Rows(vRow).Cut
Rows(vInsertRow).Insert Shift:=xlDown
'...
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
end sub