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

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


--


Gary


"Sige" wrote in message
oups.com...
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

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


--
HTH

Bob Phillips

"Gary Keramidas" wrote in message
...
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


--


Gary


"Sige" wrote in message
oups.com...
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

Hi Bob, Gary many thanks!

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

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

*** Sent via Developersdex http://www.developersdex.com ***

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

--


Gary


"SIGE" wrote in message
...
Hi Bob, Gary many thanks!

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

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

*** Sent via Developersdex http://www.developersdex.com ***