Text to Number crashing large files

Question

Below code converts text to number except values with prefix 0 I.E. 002A.

This code works on small data files but crashes Excel on large files even though I turn off calculation before code runs.

vba

Sub Text2Number()
    On Error GoTo EH

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

Set Rng = ActiveSheet.UsedRange

Rng.Cells(1, 1).Select

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Columns.Count
        If Rng.Cells(i, j) <> "" Then
            Union(Selection, Rng.Cells(i, j)).Select
        End If
    Next j
Next i
For Each c In Rng.Cells
    If IsNumeric(c.Value) And Left$(c.Value, 1) <> "0" Then
        c.NumberFormat = "General"
     c.Value = c.Value
    End If
Next
Rng.HorizontalAlignment = xlLeft
CleanUp:
    On Error Resume Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
Exit Sub
EH:
    ' Do error handling
    Resume CleanUp
End Sub

[Avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) though your code does nothing with that unionized range so you can just remove that entire nested loop. — Warcupine, Nov 29 '22 at 20:04
You don't need the `Union` at all. Also, when you find a cell to set the "General" format, you don't have to set `c.Value = c.Value`. Just changing the `NumberFormat` should be enough. Also read this on [how to avoid using `Select`](https://stackoverflow.com/a/10717999/4717755). — PeterT, Nov 29 '22 at 20:05
When you say it crashes Excel, do you mean Excel shuts down (force closes) or it just locks up and becomes unresponsive? — JohnM, Nov 29 '22 at 20:58
Thank you for comments regarding no use of select however my issue is to avoid values with prefix 0 to circumvent that I need a different approach perhaps. And thanks no union also did the trick. — Alvi, Nov 30 '22 at 15:27
@JohnM It says not responding as in I left it that way after work and next day It was still there :D — Alvi, Nov 30 '22 at 15:33

score 1 · Answer 1 · answered Nov 30 '22 at 11:07

1

Use the below code, you don't have to convert the numbers one by one, it will definitely hang your workbook

A better way is to use a dummy "text to column" on each column of your used range

I am using the Tab as delim which is not usually present in excel texts, if it is present you can use some other delimiter

Sub Text2Number_v2()

Application.Calculation = xlCalculationManual
last_col = ActiveSheet.UsedRange.Columns.Count

For col = 1 To last_col

    Columns(col).TextToColumns Destination:=Cells(1, col), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    
Next

Application.Calculation = xlCalculationAutomatic

End Sub

answered Nov 30 '22 at 11:07

usmanhaq

1,527
1
6
11

Thank you for this alternative way this was very fast only issue is If I can avoid text to number values starting with 0 i.e. '00200A – Alvi Nov 30 '22 at 15:32
1

You can replace the 0 with some character before running the code and after running the code replace them again with 0 or if they are not too many you can save them in some temporary list and can re populate them after the code ends – usmanhaq Dec 01 '22 at 04:51
I only need to avoid values with leading zeros. Perhaps I need to find another code to identify such values and then add a prefix to those which can later be removed. – Alvi Dec 01 '22 at 18:32

score 0 · Accepted Answer · answered Dec 02 '22 at 17:33

Here is how I was able to tame this macro and stop it from crashing.

Sub Text2Number()
    On Error GoTo EH

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

Dim Rng As Range

Set Rng = Application.Selection
Set Rng = Application.InputBox("Range", xTitleId, Rng.Address, Type:=8)

For Each c In Rng.Cells
    If IsNumeric(c.Value) And Left$(c.Value, 1) <> "0" Then
        c.NumberFormat = "General"
     c.Value = c.Value
    End If
Next
Rng.HorizontalAlignment = xlLeft
CleanUp:
    On Error Resume Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
Exit Sub
EH:
    ' Do error handling
    Resume CleanUp
End Sub

Text to Number crashing large files

2 Answers2