Inserting & Deleting rows can take ages in Excel 2000

[MJBDeane]

I have created an Excel application for Engineering Quality Document
(FMEAs).
These documents have: lots of merged cells, no more than 20 columns
generally less than 200 rows.

In Excel 97 the application ran fast enough not to be annoying.

The same code in Excel 2000... well you have many forced coffee break
waiting for it! (Some functions can take >20mins on a Pentium 3, 1GH
with 256Mb Ram, which used to run in less than 1 min)

I have looked at the code in great detail and have discovered that 99
+ of the time, the code is waiting for Excel to insert or delete
row.

I use the very basic lines of code:

Cells(x,y).EntireRow.Delete

Cells(x,y).EntireRow.Insert

Is there anything that can be done to speed this up

 

[Bernie Deitrick]

MJBD,

Sort your rows first based on your deletion criteria, then delete them
all at once. I have post code previously that does just that, so do a
google groups search on

deitrick entirerow.delete sort =IF group:microsoft.public.excel.*

and read the first post.

HTH,
Bernie
MS Excel MVP

 

[mudraker]

I do not use 2000 so I do not know if this will work but these are som
of the things I do to increase speed



Application.Calculation = xlCalculationManual


Application.ScreenUpdating = False

Rows(x).Delete shift:=xlUp

Rows(x).Insert shift:=xlDown


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = Tru

 

[MJBDeane]

Thanks for the tips mudraker.

I had not thought of setting calculation to xlcalculationmanual and i
should speed up some of my other macros working in sheets with lots o
formulas - much appreciated.

Forcing shift:=xlUp appears to slow down the deleting process rathe
than speed it up.

The calculation state does not appear to make any difference but tha
may be related to the number of formulas on the sheet (not many)..
this could help elsewhere.

I'm still looking for ways to speed this up..

 

[lawler]

Changing your screenview to 'normal' will also increase speed.