Very slow ClearContents or Delete for large range

**whyreless** · 08-01-2008, 12:14 PM

I wrote a small VBA program on my laptop, a Monte Carlo simulation that ran in about 10 minutes. When I then ran it on my desktop, it appeared to not be executing, but "came to life" after about 10 minutes and executed. I tracked the difference to execution of a delete command that cleared the contents of a large range of cells.

Please Login or Register  to view this content.

However, it isn't a VBA issue. When I manually select the range, and press the Delete Key, it takes about 10 minutes to clear the contents of the range, with just the hourglass cursor indicating activity. Tried the following changes, with no improvement in manual times:
- changed to manual calculation
- removed all formulas from sheet which reference range
- removed all other sheets on workbook

But it still takes several minutes to clear the contents (contents are all numeric values, no contents, range is 32,000 rows by 17 columns.

If I add a new sheet, select a similar size range, and delete, it deletes nearly instantly (the same timeframe that the original command executes on the laptop).

Excel 2003 on both machines, both on windows XP, both updated within the last week. Laptop is the older, slower machine (single core at 2.26 GHz, 1.5 GB memory), compared to the desktop (quad core at 2.4 GHz, 3 GB memory).

Any ideas on what might be causing the slower execution on the desktop, and how to remedy?

**robert111** · 08-02-2008, 02:28 PM

create a brand new empty spreadsheet, then copy the cells into it, then delete them, then tell us how long it took...

**Norie** · 08-02-2008, 03:04 PM

You don't need to 'goto' the range to clear it.

Please Login or Register  to view this content.

**whyreless** · 08-04-2008, 03:54 PM

To: Norie - thanks, that is more efficient, but the underlying problem is not VBA related...

To: robert111
I tried various versions of copying into a new spreadsheet and pasting.
- If I copy the entire worksheet, the problem persist - take ~4 minutes to clear contents
- If I copy/paste just the range, it clears in ~1 sec
- If I use copy/paste special - values only, for the entire worksheet, ~ 1 sec
- If I use copy/paste special - formulas, for the entire worksheet, ~1 sec

The existance of the problem appears to be related to copying the formats, which exist only in the "header" for the range, with respect to the impact in the delete times in the new workbook. (Formatting consists of some merged cells, background colors and borders.)

However, if I remove the formatting in the original workbook, and resave it under another name, the problem persist...; that is, in a new worksheet, it is possible to avoid the problem (whatever it is), but once it is in a workbook, it persists.

Note that this problem only occurs one of the two computers that I've used with this spreadsheet... which adds another dimension that may be helpful in figuring out what is happening.

Thanks for you thoughts and input...

**Norie** · 08-04-2008, 05:03 PM

Why isn't it VBA related?

I mean you are using VBA as far as I can see.

Perhaps you should be turning off calculation and screenupdating in the code.

**whyreless** · 08-07-2008, 06:59 PM

The problem isn't VBA related because I found that the delay occurs even when the VBA code isn't being executed.

That is, if I manually select the range and then hit the "delete" key, it takes several minutes to delete the contents of the range... Of course, it is equally slow when I execute the VBA code, but that is really just using a macro to replace the keystrokes.

Thx

**martindwilson** · 08-07-2008, 07:18 PM

prob the vba is trying to recalculate the whole work sheet when cells deleted have you tried removing excess formats?
http://support.microsoft.com/kb/244435

**Richard Schollar** · 08-08-2008, 04:11 AM

Hi

Do you have any formulas in your workbook (doesn't matter if they are not on this sheet) using INDIRECT or OFFSET? Particularly any dynamic named ranges?

Richard

**MarcLP** · 10-28-2014, 11:55 AM

I know the topic is very old, but once it haven't a solution...

I changed the clear contents to a copy empty cell wiht destination to the range I want to clear.
Its instantaneous.
Range("AAA1").Copy Destination:= Range("A5:Z100")

Very slow ClearContents or Delete for large range

LinkBack

Thread Tools

Rate This Thread

Display

Very slow ClearContents or Delete for large range

Re: Very slow ClearContents or Delete for large range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions