convert numbers stored as text, over 7 columns

[JohnExcel222]

this works on column U.

How can I amend it to work on columns O to U ? I would like to convert numbers stored as text. Thanks

Sub texttoNum6() 'Excel VBA dynamic example of the text to numbers procedure.

Dim ar As Variant
Dim var As Variant
Dim i As Long
Dim lr As Long lr = Range("u" & Rows.Count).End(xlUp).Row ar = Range("u2:u" & lr)
ReDim var(1 To UBound(ar), 1 To 1)
For i = 1 To UBound(ar) 'Start of VBA loop var(i, 1) = ar(i, 1) * 1
Next i
Range("u2:u" & lr) = var
End Sub

 

[Joe4]

You do not need to loop through each cell. You can just loop through all the columns and use Text to Columns, like this:

Sub MyNumberConvert()

    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Loop through columns O-U
    For c = 15 To 21
        Columns(c).TextToColumns Destination:=Cells(1, c), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
    Next c
    
    Application.ScreenUpdating = True
    
End Sub

 

[Fluff]

How about

Sub JohnExcel()
   With Range("O2:U" & Range("O" & Rows.Count).End(xlUp))
      .Value = .Value
   End With
End Sub

 

[Joe4]

I like Fluff's reply, it is a lot shorter and concise.
But just be aware that it may a need slight modification if your last row of data may have a blank in column O.
You would just need to change the "O" is this part:

Range("O" & Rows.Count)

to some column that will always have a value in the last row of data.

 

[JohnExcel222]

How about

Sub JohnExcel()
   With Range("O2:U" & Range("O" & Rows.Count).End(xlUp))
      .Value = .Value
   End With
End Sub

How about

Sub JohnExcel()
   With Range("O2:U" & Range("O" & Rows.Count).End(xlUp))
      .Value = .Value
   End With
End Sub

This give me a Debug failure

 

[JohnExcel222]

I like Fluff's reply, it is a lot shorter and concise.
But just be aware that it may a need slight modification if your last row of data may have a blank in column O.
You would just need to change the "O" is this part:

Range("O" & Rows.Count)

to some column that will always have a value in the last row of data.

Can you please give me the whole code? I did not understand

 

[JohnExcel222]

You do not need to loop through each cell. You can just loop through all the columns and use Text to Columns, like this:

Sub MyNumberConvert()

    Dim c As Long
   
    Application.ScreenUpdating = False
   
'   Loop through columns O-U
    For c = 15 To 21
        Columns(c).TextToColumns Destination:=Cells(1, c), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
    Next c
   
    Application.ScreenUpdating = True
   
End Sub

This worked super Many thanks Joe.

 

[Fluff]

Oops, my code should have been

Sub JohnExcel()
   With Range("O2:U" & Range("O" & Rows.Count).End(xlUp).Row)
      .Value = .Value
   End With
End Sub

 

[JohnExcel222]

Can you please give me the whole code? I did not understand

Ah ok. I got it now ... I had to read twice . Thanks guys. You are great.

 

[Fluff]

Glad we could help & thanks for the feedback.