2003: Slow Response on ClearContents

[RCL2884]

When I use ClearContents in Excel 2003 I get a very slow response, when
clearing many rows. For example, if I clear about 30 columns and 30,000 rows,
it might take 10 minutes. If I run the same procedure

For example: Range("A1:AD30000").ClearContents

on a different computer, it takes less than a second. What is going on? I
have looked through all the settings, but do not see anything obvious.

I am using XP-Pro, with a 2.66 Mhz dual core Xeon processor, with 2 GB of
ram, and so the system should not be an issue. This problem began suddenly
several months ago and has persisted through a reinstall of Office 2003. It
is driving me crazy.

Thanks,
Rick

 

[JLGWhiz]

Check you worksheet code modules for Worksheet_Change code, or any code in
the stanadard module or formulas in the worksheet cells that might trigger
from changes in the cells value and would recalculate.

 

[rleavitt]

Check you worksheet code modules for Worksheet_Change code, or any code in
the stanadard module or formulas in the worksheet cells that might trigger
from changes in the cells value and would recalculate.

Thanks for the reply. As far as I can tell I do not have any reference
to Worksheet_Change in any modules. I note that the delay on
ClearContents occurs even when automatic calculation is set to False.
Another clue... it takes longer to clear cells the farther down on the
sheet I go. For example, rows 1-100 clear quickly, but rows
20001-20100 take a long time.

 

[rleavitt]

When I useClearContentsin Excel2003I get a veryslowresponse, when
clearing many rows. For example, if I clear about 30 columns and 30,000 rows,
it might take 10 minutes. If I run the same procedure

I have found a solution to this problem (sort of). The delay is
eliminated if I issue the following command before clearing the cells.

Application.EnableEvents = False

As I understand it, an "Event" is something like opening or closing a
sheet, or other things such as clicking on or changing a cell, etc. I
have not added any code pertaining to eny event and so I am not sure
why disabling the event checking should work. As I mentioned, this
problem occurs on some computers and not others, regardless of what
workbook is open, and so it must relate to a general or global
setting. I cannot find anything in the various options that seems
relevant. Any suggestions of where to look? Since the slow response
occurs even when I clear cells in the sheet (and not by using a macro)
I would like to be able to change a setting that eliminates the
problem for good.

Thanks in advance for any suggestions.
....Rick

 

[Charles Williams]

Hi Rick,

Do you have Excel 2007 installed?
If you do not have it installed then I think its something to do with
updating the system with Microsoft Update.

Anyway Enablevents=False seems to solve the problem

Charles
_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

 

[rleavitt]

Hi Rick,

Do you have Excel 2007 installed?
If you do not have it installed then I think its something to do with
updating the system with Microsoft Update.

Nope... I am using Excel 2003 that has been fully updated. When the
problem first occurred a couple of months ago, I thought it might be
due to a recently installed update, and so I backed out the update,
but to no effect. I also reinstalled Excel 2003, and this did not
help. I will stop perseverating on this since I have a work-around. I
just wish I understood what is happening.
....Rick

 

[Charles Williams]

Hi Rick,

On my systems ( with 5 versions of XL installed) I get the following results

With Enable events switched ON:
XL97 Very fast
XL2000 Slow
XL2002 Slower
XL2003 Even slower
XL2007 Over 3 Minutes

This happens with Clear, ClearContents, and Delete

With EnableEvents switched OFF all versions are very fast.

On Saturday we tested this on a system (XL 2003 only & Win XP SP2) that did
not have the latest updates and it was fast.

Since your system does not have XL2007 installed I think we can narrow it
down to some fairly recent update.

regards
Charles
_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

 

[rleavitt]

Since your system does not have XL2007 installed I think we can narrow it
down to some fairly recent update.

Thanks for testing. I think you must be right... I thought I
uninstalled the updates that occurred around the time the issue first
appeared, but I may not have done that right. I will just turn
"EnableEvents" off as a matter of course, and turn it on, if I want to
actually to Event driven code. I will just add it to my (long) list of
Excel annoyances.
....Rick

 

[Charles Williams]

Hi Rick,

Turns out its probably the Google Desktop Office Addin.

Uninstall or switching off the COM addin seems to solve the problem.

Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com