Problems with ClearContents in Excel

rcl1960 · 09-06-2007, 06:52 PM

When using Excel 2003, it takes a very long time for me to
clear the contents of a large number of cells. The delay occurs
whether I clear the contents manually in Excel, or within a VBA
macro. This problem began several weeks ago and has persisted. For example, when I clear 20,000 rows in a moderately large workbook (12Meg) it takes 10 minutes. I ran the same macro on a co-worker’s computer (exact same workbook) and it took less than 0.2 seconds. I reinstalled Office 2003 and this did not help (no impact)

The calculate mode is set to Manual, and application.ScreenUpdating is set to False.

This is driving me crazy.... I have an up to date computer (2 meg
memory) and I am using XP pro... have disabled Google desktop (no
impact), and have very little unusual on my machine.

…one possible clue... when I use a macro that clears, say, 100 rows at
a time, and set a timer, the length of time it takes to perform the
clearcontents function increases with each subsequent call. For
example the first 100 rows takes 0.3 seconds.. the next 100 rows
takes 0.7 seconds, and so on, then next 100 takes 1.1 seconds and do on.

Thanks!

raghuramr · 10-03-2008, 10:05 AM

Got the similar problem.

Figured out that CLEAR is raising events which we need to suppress

Application.EnableEvents = False

****** Your Clear Code ****

Application.EnableEvents = True

David M58 · 10-04-2008, 10:31 AM

Information posted here may be relevant.

arianraj · 10-30-2008, 04:59 AM

The real issue is caused by the Google Office COM Add-in, below is the steps to de-activate it:

With Excel 2007 it is fairly easy to deactivate:
Goto Office Button–>Excel Options–>Addins->Com Addins and deselect Google Desktop Office Addin
With earlier versions of Excel you have to customise a toolbar and add the Com Addins dialog to it.
Goto View–>Toolbars–>Customise–>Commands tab–>Tools then about halfway down you will find Com Addins, select and drag to the toolbar of your choice.
Then you can uncheck Google Desktop Office addin.

09-06-2007, 06:52 PM	#1 (permalink)
rcl1960 Registered User Join Date: Sep 2007 Posts: 1 OS: XP Pro	Problems with ClearContents in Excel When using Excel 2003, it takes a very long time for me to clear the contents of a large number of cells. The delay occurs whether I clear the contents manually in Excel, or within a VBA macro. This problem began several weeks ago and has persisted. For example, when I clear 20,000 rows in a moderately large workbook (12Meg) it takes 10 minutes. I ran the same macro on a co-worker’s computer (exact same workbook) and it took less than 0.2 seconds. I reinstalled Office 2003 and this did not help (no impact) The calculate mode is set to Manual, and application.ScreenUpdating is set to False. This is driving me crazy.... I have an up to date computer (2 meg memory) and I am using XP pro... have disabled Google desktop (no impact), and have very little unusual on my machine. …one possible clue... when I use a macro that clears, say, 100 rows at a time, and set a timer, the length of time it takes to perform the clearcontents function increases with each subsequent call. For example the first 100 rows takes 0.3 seconds.. the next 100 rows takes 0.7 seconds, and so on, then next 100 takes 1.1 seconds and do on. Thanks!

10-03-2008, 10:05 AM	#2 (permalink)
raghuramr Registered User Join Date: Oct 2008 Posts: 1 OS: Windows XP	Re: Problems with ClearContents in Excel Got the similar problem. Figured out that CLEAR is raising events which we need to suppress Application.EnableEvents = False **** Your Clear Code ** Application.EnableEvents = True

10-04-2008, 10:31 AM	#3 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Problems with ClearContents in Excel Information posted here may be relevant.

10-30-2008, 04:59 AM	#4 (permalink)
arianraj Registered User Join Date: Sep 2008 Posts: 4 OS: Windows XP	Re: Problems with ClearContents in Excel The real issue is caused by the Google Office COM Add-in, below is the steps to de-activate it: With Excel 2007 it is fairly easy to deactivate: Goto Office Button–>Excel Options–>Addins->Com Addins and deselect Google Desktop Office Addin With earlier versions of Excel you have to customise a toolbar and add the Com Addins dialog to it. Goto View–>Toolbars–>Customise–>Commands tab–>Tools then about halfway down you will find Com Addins, select and drag to the toolbar of your choice. Then you can uncheck Google Desktop Office addin.