Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increase Speed
or
Sub chgfontname() Sheets.Select Cells.Select Selection.Font.Name = "arial" Range("a1").Select Sheets(1).Select End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... 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 wrote in message oups.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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increase Speed
or for speed, skip the selecting:
sub chgfontname() sheets(1).font.name="arial" end sub "Don Guillett" wrote in message ... or Sub chgfontname() Sheets.Select Cells.Select Selection.Font.Name = "arial" Range("a1").Select Sheets(1).Select End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... 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 wrote in message oups.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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increase Speed
and for the rest of the sheets?
-- Don Guillett SalesAid Software "Jef Gorbach" wrote in message ... or for speed, skip the selecting: sub chgfontname() sheets(1).font.name="arial" end sub "Don Guillett" wrote in message ... or Sub chgfontname() Sheets.Select Cells.Select Selection.Font.Name = "arial" Range("a1").Select Sheets(1).Select End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... 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 wrote in message oups.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I increase speed of arrow buttons to move from cell to cel. | Excel Discussion (Misc queries) | |||
Increase Speed of Calculations | Excel Worksheet Functions | |||
Number in cell increase with increase in font size. | Excel Discussion (Misc queries) | |||
How to increase calculations speed in pivot table with calculated fields & items | Excel Discussion (Misc queries) | |||
Increase macro speed? | Excel Programming |