slow macro help

[DPingger]

I am using the following to clear out 0 entries:

Dim cl As Range, myRange As Range
Set myRange = Range("A2:BV2348")
For Each cl In myRange.Cells
If cl.Value = 0 Then cl.ClearContents
Next cl

Is there a way to make this faster or a different approach?
This is taking more than 45 minutes, I don't know why.

TIA

DPingger

 

[Mike H]

Try this

Dim cl As Range, myRange As Range
Set myRange = Range("A2:BV2348")
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For Each cl In myRange.Cells
If cl.Value = 0 Then cl.ClearContents
Next cl
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Mike

 

[Nigel]

Maybe turn off calculation and screen updating before you run your code?

With Application
.Calculation = xlManual
.ScreenUpdating = False
' your code
.ScreenUpdating = True
.Calculation = xlAutomatic
End With

 

[Dave Peterson]

How about recording a macro when you select that range
Edit|Replace
what: 0
with: (leave blank)
replace all

Make sure you're looking at entire contents, too.

 

[Evan Johnson]

Mike, Nigel, and Dave are all correct with turning off screen updating.

That can produce a substantial increase in calculation time. Especially if
you are using a general code to evaluate a significantly large range (or even
an entire sheet).

I wrote a macro to erase all carriage returns, trim out extra spaces, then
resize all columns to auto fit their widest cell entry. The idea was to
clean up financial reports from various companies into a "clean format" so
other macros or worksheet functions worked as expected. Originally I didn't
have screen updating turned off and the macro could take several minutes to
run, added one line of code and presto...down to around 10 seconds. This
various of course but the idea is the same.

 

[Nigel]

I am not sure that Dave Peterson suggested turning off screen updating, what
he did suggest is to use search and replace, a good idea in my view rather
than using code to trawl through cell by cell.

I have not done any tests as to which is faster but I suspect the internal
Excel code is going to be faster?

--

Regards,
Nigel
(e-mail address removed)

 

[DPingger]

Mike, Nigel, Dave and Evan.

Thank you all. Worked like a charm.

You guys are sanity savers.

DPingger

 

[Evan Johnson]

Nigel,

My bad...you caught me. I read through the post rather quickly and I when I
saw the screen updating reference I just wanted to quickly add to the point.
Thanks for keeping me in check.

I just wanted to emphasize the ease and use of that particular code.

I also haven't tried the turning off calculation bit, but I will definately
try adding that in my own work. I've had some projects effectively lock up a
computer from massive and multiple vlookups and it would very helpful to turn
them off, perform some formatting, then turn them back on. Thanks for the tip