Clearing cells takes long, long time
 

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

Clearing cells takes long, long time
 

I have the same problem with Excel 2003. Anyone can help?

"unclemuffin" wrote:

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

Clearing cells takes long, long time
 

Use Access or a faster computer?

unclemuffin wrote:

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

Clearing cells takes long, long time
 

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.

unclemuffin wrote:

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

--

Dave Peterson

Clearing cells takes long, long time
 

In . com,
unclemuffin spake thusly:

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=

Clearing cells takes long, long time
 

On Aug 14, 7:07 pm, Dave Peterson wrote:
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:

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

--

Dave Peterson

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

Clearing cells takes long, long time
 

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.





unclemuffin wrote:

On Aug 14, 7:07 pm, Dave Peterson wrote:
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:

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

--

Dave Peterson

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

--

Dave Peterson

Clearing cells takes long, long time
 

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?

"Dallman Ross" wrote:

In . com,
unclemuffin spake thusly:

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=

Clearing cells takes long, long time
 

#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!
"Jerry" wrote:

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?

"Dallman Ross" wrote:

In . com,
unclemuffin spake thusly:

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=

Clearing cells takes long, long time
 

The response I gave didn't help?

Jerry wrote:

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?

"Dallman Ross" wrote:

In . com,
unclemuffin spake thusly:

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=


--

Dave Peterson