Code run speed - Move trailing minus sign

[rossi789]

I came across the below code to fix a trailing minus sign in some data. The workbook is intended to be a template with a series of calculations from the data on 5 other worksheets. Running the code in a separate workbook completes smoothly and within a second or two. However running it in the workbook including calculations takes forever. Could this be because excel is recalculating all formulas for each change that is made?

Is there a change that could be made to fix this?

Sub Negsignleft()    
    Dim cell As Range
    Dim rng As Range
    ''move minus sign from right to left on entire worksheet
    On Error Resume Next
    Set rng = ActiveSheet.Range("E1:F15000"). _
            SpecialCells(xlCellTypeConstants, xlTextValues)
    On Error GoTo 0
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            cell.Value = CDbl(cell.Value)
        End If
    Next cell
End Sub

Thanks

 

[Rick Rothstein]

This should execute much faster for you...

Sub Negsignleft()
  Columns("E").TextToColumns TrailingMinusNumbers:=True
  Columns("F").TextToColumns TrailingMinusNumbers:=True
End Sub

 

[rossi789]

That worked perfectly. Thank you.

 

[Johnny C]

For future reference, you can turn calculations off or on very simply

Application.Calculation = xlCalculationManual

turns it off.

Application.Calculation = xlCalculationAutomatic

turns it on

There's another rarely used option which sets calculations on for everything except tables, only used when you have large (100K rows+) with chunky calculated columns

Application.Calculation = xlCalculationSemiautomatic

If you have a workbook that needs to be kept manual (e.g. they have external data connections which refresh every time you do a calculate) you can force a recalculate before a save:-

Application.CalculateBeforeSave = True