slow .Clear in 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brzak - 03 Dec 2007 15:08 GMT

Is anybody else experiencing huge delys with the the .Clear method?
and more importantly, are there any magical tips for it?

Let's take an example: Range("Sheet2!C10:C40000").Clear

takes 1minute 54 seconds(yes, i timed it with a stopwatch). This is an
average of 3 trials, each of which took a similar amount of time.

True this is running on a laptop with a Centrino Duo (2x 1.06 GHz
processors) and with excel only ever using one of these, it doesn't
help.

The same code on a laptop running excel 2003 (single 1.3 GHz
processor) is almost instant, a few seconds at most.

I don't really expect anybody to come up with something to solve this,
but rather to tell me that there is a problem with excel?

Reply to this Message

Charles Williams - 03 Dec 2007 15:49 GMT

The solution is Enablevents

Application.EnableEvents=False
Range("Sheet2!C10:C40000").Clear
Application.EnableEvents=True

It happens with both Clear and delete

This slowdown seems to have been caused by some very recent Microsoft
Update, it happens with all XL versions except XL97 if the system has all
the most recent updates.

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

> Is anybody else experiencing huge delys with the the .Clear method?
> and more importantly, are there any magical tips for it?
[quoted text clipped - 13 lines]
> I don't really expect anybody to come up with something to solve this,
> but rather to tell me that there is a problem with excel?

Reply to this Message

brzak - 05 Dec 2007 16:15 GMT

Thanks for the tip Charles. Unfortunately, I found an alternative
solution before reading you're post.

The problem I was having was with the a Google Desktop Add-In for
Office 2007.

Perhaps i posted in the wrong section as it is not just a programming
issue but rather an issue with selecting large amounts of cells.
However, it does not arise when working with spreadsheets native to
the 2007 format, only spreadsheets created by excel 1997-2003, though
not true for all sheets strangely. E.g. select one row and there is a
one second pause, in which excel does not respond, if I select a
number of rows(or: a number columns, a large(ish) area A1:CC20,000)
then it really is unbearable, getting to the stage where selecting
about 100 rows will freeze excel for longer than I was ever prepared
to wait, i.e. crashing it, and i am a patient person at times.

I couldn't quite pinpoint the exact cause, with my particular workbook
(created with excel 2003) some of the sheets were fine, whereas others
were not. Is it something to do with formatting? I don't know, I
suppose I could reinstall the Add-In but I'm so glad to have some
level of speed back that I just cannot bring myself to do it!

On Dec 3, 3:49 pm, "Charles Williams" <Char...@DecisionModels.com>
wrote:

> The solution is Enablevents
>
[quoted text clipped - 29 lines]
> > I don't really expect anybody to come up with something to solve this,
> > but rather to tell me that there is a problem with excel?

Reply to this Message

brzak - 05 Dec 2007 16:18 GMT

somebody else came across the same problem / solution here:

http://groups.google.com/group/Google-Desktop_Something-Broken/browse_thread/thr
ead/6bf70d663a949017/ab615b3724c4462c?q=%22Excel+2007%22&lnk=sbp&fwc=2

> Thanks for the tip Charles. Unfortunately, I found an alternative
> solution before reading you're post.
[quoted text clipped - 59 lines]
> > > I don't really expect anybody to come up with something to solve this,
> > > but rather to tell me that there is a problem with excel?

Reply to this Message

Charles Williams - 05 Dec 2007 18:47 GMT

Thanks for the tip about Google Desktop.
I tested it and agree it seems to be the cause of the problem. If you
uninstall it the problem goes away.

Actually you can just get rid of the Google Office Com Addin:
Customise a toolbar, select Tools and add the Com Addins button to the tool
bar by dragging it to the toolbar.
Then select Google Office Search and uncheck it.

I guess what its doing is setting up and running a workbook level event on
sheet change that can chew up a LOT of cpu cycles.
Using .EnableEvents=false prevents this from happening, but its better to
just get rid of it.

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

> somebody else came across the same problem / solution here:
>
[quoted text clipped - 67 lines]
>> > > this,
>> > > but rather to tell me that there is a problem with excel?

Reply to this Message

brzak - 07 Dec 2007 09:38 GMT

Initially, it was tricky to work out how to uninstall an Add-In.

I did it by going to Excel Options, selecting the Add-Ins tab, then
from the "Manage:" drop down list select COM Add-Ins and click Go, and
just deselect the GD Add-In.

From what I recall, this would have been a lost easier if there was a
clearer relationship between the drop down list and the display above.

Luckily it is a separate Add-In for Outlook, i'm not sure what the
excel add-in was for but the outlook add-in makes searching email a
lot easier. It's funny, it *seems* like such a simple thing but the
built in search for Outlook just cannot be compared to GD search.

On Dec 5, 6:47 pm, "Charles Williams" <Char...@DecisionModels.com>
wrote:

> Thanks for the tip about Google Desktop.
> I tested it and agree it seems to be the cause of the problem. If you
[quoted text clipped - 15 lines]
> FastExcel 2.3
> Name Manager 4.0http://www.DecisionModels.com

Reply to this Message