problems with ClearContents in Excel 2003

[rleavitt]

Help! 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-workers computer
(exact
same workbook) and it took less than 0.2 seconds. I reinstalled
Office
2003 and this did not help (no impact)

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.6 seconds, and so on.


Any suggestions will be greatly appreciated.
....Rick

 

[Roger Govier]

Hi

You could switch Calculation to Manual before running the macro,
Tools>Options>Calculation>Manual
then switch back to automatic afterwards.

 

[rleavitt]

Thanks for the reply. The calculation is set to Manual. It is not
recalculating... I have been monitoring and timing that separately.
It is doing something else. I just don't know what it is doing. I
thought about turning off the Undo function, but I don't know how to
do that... but note that there is no long time lag when I run this on
someone else's computer, so I don't think it is just the Undo
function.

 

[Earl Kiosterud]

Rick,

If there's a lot of calculation going on as a result of your clearing the cells, try this:

Application.Calculation = xlCalculationManual
' your cell-clearing code here
Application.Calculation = xlCalculationAutomatic

There's a danger here. If an error stops your code, calculation will remain set to manual.
Not good. If there's any chance of that, then error processing code should reset
calculation to automatic. Something like:

On Error GoTo errr
Application.Calculation = xlCalculationManual
' your cell-clearing code here
Application.Calculation = xlCalculationAutomatic
End

errr:
Application.Calculation = xlCalculationAutomatic
MsgBox "Error " & Err.Number & " has occurred. " & Err.Description
End
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[rleavitt]

Thanks for the reply.

The slowdown occurs with calculation already set to manual. Just to
make sure I was not missing something I did try adding the suggested
code to no effect. This is not a calculation issue, but something
else. generally since there are a lot of calculations within the
workbook, I leave the calculation setting to manual as the default so
that I can control when the calculation occurs.

 

[Dave Peterson]

I'm not sure if this will help, but it shouldn't take long to test.

Saved from a previous post.

If you can see the pagebreak dotted lines, then excel will slow down.
If you're in View|Page break preview mode, then excel will slow down.

Turning off .screenupdating and changing the .calculationmode to manual may help
speed things up:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'your code here

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode
Application.ScreenUpdating = True

End Sub

 

[rleavitt]

Thanks! regretably, turning off the updating or calculation had no
impact. If I clear 5000 rows, 500 at a time, it takes 0.4 seconds for
the first 500 rows and 6.4 seconds for the last 500, and more than 34
seconds for all 5000. Obviously something is wrong.

 

[Dave Peterson]

This did more than turn off calculation.

Did you try it?

 

[rleavitt]

I used exactly the code you described, and there was no impact. 35
seconds to clear 5000 rows seems much too long. Note the pattern of
more time elapsed the farther down in the sheet.


My Code:
Sub MyClear()
sName = "Resrv95"
Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ClearRow = 32
nclear = 500
t0 = Timer()
t1 = t0
OutRow = 3
While ClearRow <= 5000
clearRow2 = ClearRow + nclear
TheRange = "A" & Format(ClearRow, "#") & ":CZ" & Format(clearRow2,
"#")
Worksheets(sName).Range(TheRange).ClearContents
ClearRow = clearRow2
t2 = Timer()
aud.Cells(OutRow, 20) = t2 - t1
aud.Cells(OutRow, 21) = t2 - t0
aud.Cells(OutRow, 19) = ClearRow
OutRow = OutRow + 1
t1 = t2
Wend
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode
Application.ScreenUpdating = True
End Sub

The output:

532 0.44 0.44
1032 1.14 1.58
1532 1.81 3.39
2032 2.47 5.86
2532 3.13 8.98
3032 3.80 12.78
3532 4.48 17.27
4032 5.14 22.41
4532 5.80 28.20
5032 6.44 34.64

 

[Dave Peterson]

Try adding one more line to see if that helps.

Right after the xlNormalview line, but before your real code:

ActiveSheet.DisplayPageBreaks = False

I meant to include that in the original suggestion, but didn't.

 

[rleavitt]

Thanks for your interest. There was no impact from that suggestion.
still 35 seconds to clear 5000 rows.

Try adding one more line to see if that helps.

Right after the xlNormalview line, but before your real code:

ActiveSheet.DisplayPageBreaks = False

I meant to include that in the original suggestion, but didn't.

I used exactly the code you described, and there was no impact. 35
seconds to clear 5000 rows seems much too long. Note the pattern of
more time elapsed the farther down in the sheet.

My Code:
Sub MyClear()
sName = "Resrv95"
Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ClearRow = 32
nclear = 500
t0 = Timer()
t1 = t0
OutRow = 3
While ClearRow <= 5000
clearRow2 = ClearRow + nclear
TheRange = "A" & Format(ClearRow, "#") & ":CZ" & Format(clearRow2,
"#")
Worksheets(sName).Range(TheRange).ClearContents
ClearRow = clearRow2
t2 = Timer()
aud.Cells(OutRow, 20) = t2 - t1
aud.Cells(OutRow, 21) = t2 - t0
aud.Cells(OutRow, 19) = ClearRow
OutRow = OutRow + 1
t1 = t2
Wend
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode
Application.ScreenUpdating = True
End Sub

The output:

 

[Dave Peterson]

Sorry it didn't work.

Thanks for your interest. There was no impact from that suggestion.
still 35 seconds to clear 5000 rows.

 

[Joe Himmelberg]

This isn't going to help you much, but you are not alone. A co-worker and myself have the exact same problem. We've tried everything you have. We consider ourselves more than proficient Excel and VB folks. And we are completely stumped.

Here's an oddity: I have searched the net far and wide and the ONLY references I can find to similar problems (and there are only a few) are very recent - the last couple of weeks....don't know what that means, but it's odd.

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com