slow .Clear in 2007 - microsoft.public.excel.programming

Message from discussion slow .Clear in 2007

Charles Williams

View profile

More options Dec 3 2007, 10:49 am

Newsgroups: microsoft.public.excel.programming

From: "Charles Williams" <Char...@DecisionModels.com>

Date: Mon, 3 Dec 2007 15:49:16 -0000

Local: Mon, Dec 3 2007 10:49 am

Subject: Re: slow .Clear in 2007

  Reply to author
  |
  Forward
  | Print
  | View thread
  | Show original
  | Report this message
  | Find messages by this author
  

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-a8dd-1a7a5731f6b6@d21g2000prf.googlegroups.com...

> 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 author Forward