Speed up Code?

[Sige]

Hi All,

In order to clean up some downloaded figures I run the following sub.
It works fine!!!

Sometimes I have the impression though that routine is calculating
rather slow.
It gets through but ...it takes
(sometimes looots of..)time.

Is there a way to speed up the routine???

Public Sub BlanksToNumbers()
Dim r As Range
Dim w As Worksheet
On Error GoTo err
Application.ScreenUpdating = False
For Each w In ActiveWorkbook.Worksheets
For Each r In w.UsedRange.Cells
r.Cells.Font.Name = "Arial"
If r.TEXT = "" And r.Formula = "" Then r.Value = ""
Next r
Next w
Application.ScreenUpdating = True
err:
End Sub

Cheeers Sige

 

[Gary Keramidas]

you can try this at the beginning
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False

and this at the end

With Application
CalcMode = .Calculation
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True

 

[Bob Phillips]

I think (hope) that you mean

you can try this at the beginning
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False

and this at the end

With Application
.Calculation = CalcMode
.ScreenUpdating = True

 

[SIGE]

Hi Bob, Gary many thanks!

Gary: I'll take Bob's solution ;o)

"NOSPAM" to be removed for direct mailing...

 

[Gary Keramidas]

use whichever one you want, just let us know if it works or not<g>