Fast convert of Textformat to Numberformat in Excel - VBA

Question

I'm looking for a code that will convert the format of cells for large amount of cells with numbers. The procedure I apply is that I write the number "1" to an empty cell, copy it and then multiply the whole selection, where numbers are stored as text. That is really fast compared to the automatic "Convert to Number" suggestion which offers Excel (which takes ages for a Selection of 1000+ cells). I wrote this macro (it works, but is not as fast as the above mentioned procedure):

Option Explicit

Sub nasobeni()

Dim cell As Range
Dim one As Integer

one = 1

For Each cell In Selection
    cell.value = cell.value * one
Next

Application.CutCopyMode = False
Selection.NumberFormat = "0"

End Sub

Has anybody some suggestion, which would speed up this process? Thanks

For starters, I would add the usual VBA speed ups to the code: `Application.ScreenUpdating = FALSE` and `Application.Calculation = xlCalculationManual` then turn them back on at the end of the sub and see how that impacts it — Glitch_Doctor, Oct 05 '18 at 10:26

score 1 · Answer 1 · answered Oct 05 '18 at 10:28

Ensure a General cell number format then Text to Columns, Fixed width, Finish.

with selection.columns(1)
    .numberformat = "General"
    .TextToColumns Destination:=.cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
end with

Text to Columns can only process one column at a time but it is a small matter to cycle through columns.

dim i as long

with selection
    .numberformat = "General"
    for i=1 to .columns.count
        .columns(i).TextToColumns Destination:=.columns(i).cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
    end with
end with

Ron Rosenfeld · Accepted Answer · 2018-10-05T11:53:55.000

After setting the range and changing the format, merely write the Value back to the range.

Option Explicit
Sub colaTextToNumbers()
    Dim R As Range

'Can be set in many different ways
Set R = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) 'for column A

'Set R = Selection
'Set R = whatever

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

With R
    .EntireColumn.NumberFormat = "General" 'or could limit this just to R, not entire column
    .Value = .Value
End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Fast convert of Textformat to Numberformat in Excel - VBA

2 Answers2