Convert Text to Number in VBA Array

[roykana]

Dear All Master,
I want help modifying my vba code below because it is still slow and the row record is above 200000

and also a problem with the results if the table data in the "eg" sheet is filtered then the result is wrong

so I want a very fast vba array code . So there are 2 process options, first convert text to number which is

only filtered and secondly convert vba all in the "g" column in a filtered state and without filter as well.

Option Explicit
Sub texttoNum()
Dim ar As Variant
Dim var As Variant
Dim i As Long
Dim lr As Long

lr = Sheets("cth").Range("G" & Rows.Count).End(xlUp).Row
ar = Range("G2:G" & 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
Sheets("cth").Range("g2:g" & lr) = var

End Sub

Thanks
kana

 

[Alex Blakenburg]

Do you want to see it this works for you ?

Sub texttoNum()
    Dim lr As Long
    Dim lc As Long
    Dim rng As Range
    Dim sht As Worksheet
    
    Set sht = Sheets("cth")
    With sht
        lr = .Range("G" & .Rows.Count).End(xlUp).Row
        lc = .Range("G" & .Columns.Count).End(xlToLeft).Column
        Set rng = .Range("G2:G" & lr)
        .Cells(1, lc + 1) = 1
        .Cells(1, lc + 1).Copy
        Set rng = rng.SpecialCells(xlCellTypeVisible)
        rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        .Cells(1, lc + 1).ClearContents
    End With
    
End Sub

 

[roykana]

Do you want to see it this works for you ?

Sub texttoNum()
    Dim lr As Long
    Dim lc As Long
    Dim rng As Range
    Dim sht As Worksheet
   
    Set sht = Sheets("cth")
    With sht
        lr = .Range("G" & .Rows.Count).End(xlUp).Row
        lc = .Range("G" & .Columns.Count).End(xlToLeft).Column
        Set rng = .Range("G2:G" & lr)
        .Cells(1, lc + 1) = 1
        .Cells(1, lc + 1).Copy
        Set rng = rng.SpecialCells(xlCellTypeVisible)
        rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        .Cells(1, lc + 1).ClearContents
    End With
   
End Sub

dear

Alex Blakenburg​

Thank you for your reply. But the vba code you are still providing is still a bit slow. Can you make the code into a vba array code so that it becomes very fast.

Thanks
roykana

 

[Alex Blakenburg]

Your original code does not appear to check if a row is visible or not.
If you can apply it to all cells test the speed of these manually.

• Try copy paste special multiply by 1, to see if it’s faster if it doesn’t have to worry about hidden rows
• Try selecting the column and do text to columns, to see if that is faster

 

[roykana]

Your original code does not appear to check if a row is visible or not.
If you can apply it to all cells test the speed of these manually.

• Try copy paste special multiply by 1, to see if it’s faster if it doesn’t have to worry about hidden rows

What if this means I don't understand

• Try selecting the column and do text to columns, to see if that is faster

I've tried this faster
Dear Alex Blakenburg

Sub texttoNum()
    Dim lr As Long
    Dim lc As Long
    Dim rng As Range
    Dim sht As Worksheet
    
    Set sht = Sheets("cth")
    With sht
        lr = .Range("G" & .Rows.Count).End(xlUp).Row
        lc = .Range("G" & .Columns.Count).End(xlToLeft).Column
        Set rng = .Range("G2:G" & lr)
        .Cells(1, lc + 1) = 1
        .Cells(1, lc + 1).Copy
        Set rng = rng.SpecialCells(xlCellTypeVisible)
        rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        .Cells(1, lc + 1).ClearContents
    End With
    
End Sub

1. From your code how to vba code if I want to change invisible row

Option Explicit
Sub texttoNum()
Dim ar As Variant
Dim var As Variant
Dim i As Long
Dim lr As Long

lr = Sheets("cth").Range("G" & Rows.Count).End(xlUp).Row
ar = Range("G2:G" & 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
Sheets("cth").Range("g2:g" & lr) = var

End Sub

2. From my original code how is the vba code if I want to change visible and invisible row
.
Thanks
roykana

 

[Alex Blakenburg]

It's 3:30 am here.
Can I confirm you want to change all rows ?
If yes, can we take the filter off before doing the conversion?

 

[rlv01]

If the intent of this line

var(i, 1) = ar(i, 1) * 1

is to force a conversion from text to numeric, then this might work

Sub texttoNum2()
    Dim MyRange As Range
    Dim ar As Variant
    Dim i As Long
    
    With Sheets("cth")
        If .AutoFilterMode Then
            .AutoFilter.ShowAllData
        End If
        Set MyRange = .Range("G2:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
    End With
    
    MyRange.NumberFormat = "General"
    ar = MyRange.Value
    
    For i = LBound(ar) To UBound(ar) 'Start of VBA loop
        Select Case TypeName(ar(i, 1))
            Case "Double"
            Case "Empty"
            Case Else
                If VBA.IsNumeric(ar(i, 1)) Then
                    ar(i, 1) = Val(ar(i, 1)) 'convert value to number
                Else
                    ar(i, 1) = 0             'optional value if cell contents cannot be converted to a number
                End If
        End Select
    Next i
    MyRange.Value = ar
End Sub

 

[Alex Blakenburg]

roykana,
I am unclear as to whether you want all rows converted or are differentiating between visible and hidden rows.
As you have already discovered when you ran the Text to Column text I suggested, it is probably the fastest way.

It converts ALL rows regardless of whether or not they are hidden (Filtered out).
So if that is the intention then this should work for you.

Sub texttoNum_textToCol()
    'Uses Text To Column - Does all rows both visible and hidden
    Dim lr As Long
    Dim rng As Range
    Dim sht As Worksheet
   
    Set sht = Sheets("cth")
    With sht
        lr = .Range("G" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("G2:G" & lr)
           
        rng.TextToColumns Destination:=rng.Cells(1, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True

    End With
   
End Sub

 

[roykana]

roykana,
I am unclear as to whether you want all rows converted or are differentiating between visible and hidden rows.
As you have already discovered when you ran the Text to Column text I suggested, it is probably the fastest way.

It converts ALL rows regardless of whether or not they are hidden (Filtered out).
So if that is the intention then this should work for you.

Sub texttoNum_textToCol()
    'Uses Text To Column - Does all rows both visible and hidden
    Dim lr As Long
    Dim rng As Range
    Dim sht As Worksheet
  
    Set sht = Sheets("cth")
    With sht
        lr = .Range("G" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("G2:G" & lr)
          
        rng.TextToColumns Destination:=rng.Cells(1, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True

    End With
  
End Sub

Dear

Alex Blakenburg​

For those who are filtered, you can use this but for the number of row records that cannot be the solution, you must first unfilter. Ok thank you very much for your reply
I mark the solution on your behalf
thanks
roykana

 

[Alex Blakenburg]

Thank you for the feedback. Let me know if you want me to add some code to unfilter the data first.