Clearing cells takes long, long time

U

unclemuffin

I am using Excel 2007 and I have a sheet that has about 22,000 rows of
data. When I try to delete the data by all of the following methods
it takes well over three minutes to complete:

Highlight the data range and press the delete key
Highlight the data range and click Home, Clear, All or Home, Clear,
Contents
Hightlight the rows and press delete key of right click and delete
rows

How can I speed this up?

Brent
 
D

Dave Peterson

Any chance that your worksheet has an event that's firing?

It try this:
Open your workbook
Hit Alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = false

Then back to excel to test the speed.

After it's done (good or bad), you'll want to enableevents again:
Hit Alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = True

If that doesn't work, how about selecting smaller ranges and doing the clear in
little pieces.
 
D

Dallman Ross

In <[email protected]>,
unclemuffin said:
I am using Excel 2007 and I have a sheet that has about 22,000 rows of
data. When I try to delete the data by all of the following methods
it takes well over three minutes to complete:

Try going into Options / Calculation and switching it to manual
mode.

If that helps, you can turn calculation mode off in a macro that helps
you delete, then turn it back on.

=dman=
 
U

unclemuffin

Any chance that your worksheet has an event that's firing?

It try this:
Open your workbook
Hit Alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = false

Then back to excel to test the speed.

After it's done (good or bad), you'll want to enableevents again:
Hit Alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = True

If that doesn't work, how about selecting smaller ranges and doing the clear in
little pieces.

unclemuffinwrote:

That did the trick and solved my problem. How do I determine which
event is firing?
 
D

Dave Peterson

Open the workbook.
Hit alt-f11 to get to the VBE
look under that worksheet module for worksheet events
look under ThisWorkbook for workbook events

And look through other projects for application events.

I don't know a way of knowing without a little searching.

But if you open excel in safe mode:
close excel
windows start button|Run
excel /safe

All macros (including all events) will be disabled.

Then file|open your workbook
and clear the range.

Save and close your workbook

close excel and restart normally.
 
J

Jerry

Thank you so much. Adding "application.enableevents=false" in the codes also
solve my problem.

But how do I know which event is back firing when I clear contents of a
spreadsheet?
 
J

JLatham

#1 - is this curing the problem you had that you asked about regarding
running faster on one machine than on another?

#2 - be sure to turn .enableevents back on at some point or the workbook may
not work properly later:
Application.EnableEvents=True
in the code somewhere.

As for figuring out which events are firing, to quote Dave Peterson from
earlier in this discussion:
quote:
Open the workbook.
Hit alt-f11 to get to the VBE
look under that worksheet module for worksheet events
look under ThisWorkbook for workbook events

And look through other projects for application events.

I don't know a way of knowing without a little searching.
end quote

If you find events with code in them, you could add this code to the
beginning of them to get them to tell you what's firing, substituting the
real event name where I show [event name]

MsgBox "Event [event name] triggered"

It'll probably get annoying as hell at some point, because you've probably
got one event that's firing zillions of times during the clear operation.
But you'll sure have an idea of which one it is!
 
D

Dave Peterson

The response I gave didn't help?
Thank you so much. Adding "application.enableevents=false" in the codes also
solve my problem.

But how do I know which event is back firing when I clear contents of a
spreadsheet?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top