Increase Speed

Hi There,

Normally underneath macro runs quite smoothly.
But sometimes this macro takes ages to complete...not that it bugs but
the calculation speed is like litterally 100 times slower. (for the
same range-sizes!)
All I can do is Close Excel and restart the marco ... to let it run at
normal speed.
Any Ideas? -For Improvement?

Option Explicit

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

Cheers, Sige

this should change the font for the entire workbook which should be better
than changing part of each worksheet.

Sub changefontinworkbook()
Sheets.Select
Sheets(1).Activate
Cells.Select
With Selection.Font
.Name = "arial"
End With
Range("A1").Select
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
<SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1115219463.940165.284430@f14g2000cwb.googlegroups.com...
> Hi There,
>
> Normally underneath macro runs quite smoothly.
> But sometimes this macro takes ages to complete...not that it bugs but
> the calculation speed is like litterally 100 times slower. (for the
> same range-sizes!)
> All I can do is Close Excel and restart the marco ... to let it run at
> normal speed.
> Any Ideas? -For Improvement?
>
> Option Explicit
>
> 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
>
> Cheers, Sige
>

or
Sub chgfontname()
Sheets.Select
Cells.Select
Selection.Font.Name = "arial"
Range("a1").Select
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
"Don Guillett" <donaldb@281.com> wrote in message
news:uw37JEMUFHA.3184@TK2MSFTNGP15.phx.gbl...
> this should change the font for the entire workbook which should be better
> than changing part of each worksheet.
>
> Sub changefontinworkbook()
> Sheets.Select
> Sheets(1).Activate
> Cells.Select
> With Selection.Font
> .Name = "arial"
> End With
> Range("A1").Select
> Sheets(1).Select
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
> news:1115219463.940165.284430@f14g2000cwb.googlegroups.com...
> > Hi There,
> >
> > Normally underneath macro runs quite smoothly.
> > But sometimes this macro takes ages to complete...not that it bugs but
> > the calculation speed is like litterally 100 times slower. (for the
> > same range-sizes!)
> > All I can do is Close Excel and restart the marco ... to let it run at
> > normal speed.
> > Any Ideas? -For Improvement?
> >
> > Option Explicit
> >
> > 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
> >
> > Cheers, Sige
> >
>
>

or for speed, skip the selecting:

sub chgfontname()
sheets(1).font.name="arial"
end sub

"Don Guillett" <donaldb@281.com> wrote in message
news:ez4N9JMUFHA.4056@TK2MSFTNGP15.phx.gbl...
> or
> Sub chgfontname()
> Sheets.Select
> Cells.Select
> Selection.Font.Name = "arial"
> Range("a1").Select
> Sheets(1).Select
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Don Guillett" <donaldb@281.com> wrote in message
> news:uw37JEMUFHA.3184@TK2MSFTNGP15.phx.gbl...
> > this should change the font for the entire workbook which should be
better
> > than changing part of each worksheet.
> >
> > Sub changefontinworkbook()
> > Sheets.Select
> > Sheets(1).Activate
> > Cells.Select
> > With Selection.Font
> > .Name = "arial"
> > End With
> > Range("A1").Select
> > Sheets(1).Select
> > End Sub
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
> > news:1115219463.940165.284430@f14g2000cwb.googlegroups.com...
> > > Hi There,
> > >
> > > Normally underneath macro runs quite smoothly.
> > > But sometimes this macro takes ages to complete...not that it bugs but
> > > the calculation speed is like litterally 100 times slower. (for the
> > > same range-sizes!)
> > > All I can do is Close Excel and restart the marco ... to let it run at
> > > normal speed.
> > > Any Ideas? -For Improvement?
> > >
> > > Option Explicit
> > >
> > > 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
> > >
> > > Cheers, Sige
> > >
> >
> >
>
>

and for the rest of the sheets?

--
Don Guillett
SalesAid Software
donaldb@281.com
"Jef Gorbach" <Jefgorbach@aol.com> wrote in message
news:OVYBnuMUFHA.584@TK2MSFTNGP15.phx.gbl...
> or for speed, skip the selecting:
>
> sub chgfontname()
> sheets(1).font.name="arial"
> end sub
>
> "Don Guillett" <donaldb@281.com> wrote in message
> news:ez4N9JMUFHA.4056@TK2MSFTNGP15.phx.gbl...
> > or
> > Sub chgfontname()
> > Sheets.Select
> > Cells.Select
> > Selection.Font.Name = "arial"
> > Range("a1").Select
> > Sheets(1).Select
> > End Sub
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "Don Guillett" <donaldb@281.com> wrote in message
> > news:uw37JEMUFHA.3184@TK2MSFTNGP15.phx.gbl...
> > > this should change the font for the entire workbook which should be
> better
> > > than changing part of each worksheet.
> > >
> > > Sub changefontinworkbook()
> > > Sheets.Select
> > > Sheets(1).Activate
> > > Cells.Select
> > > With Selection.Font
> > > .Name = "arial"
> > > End With
> > > Range("A1").Select
> > > Sheets(1).Select
> > > End Sub
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > donaldb@281.com
> > > <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
> > > news:1115219463.940165.284430@f14g2000cwb.googlegroups.com...
> > > > Hi There,
> > > >
> > > > Normally underneath macro runs quite smoothly.
> > > > But sometimes this macro takes ages to complete...not that it bugs
but
> > > > the calculation speed is like litterally 100 times slower. (for the
> > > > same range-sizes!)
> > > > All I can do is Close Excel and restart the marco ... to let it run
at
> > > > normal speed.
> > > > Any Ideas? -For Improvement?
> > > >
> > > > Option Explicit
> > > >
> > > > 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
> > > >
> > > > Cheers, Sige
> > > >
> > >
> > >
> >
> >
>
>