slow .Clear in 2007

[brzak]

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?

[Charles Williams]

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

"brzak" <brz...@gmail.com> wrote in message
news:9823a81a-edea-48d5...@d21g2000prf.googlegroups.com...

[brzak]

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
>
> 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 Novhttp://www.exceluserconference.com/UKEUC.html

[brzak]

somebody else came across the same problem / solution here:

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

[Charles Williams]

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

"brzak" <brz...@gmail.com> wrote in message

news:bff238fe-0acd-4af4...@i12g2000prf.googlegroups.com...

[brzak]

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: