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

 

[roykana]

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

please can you add the code to clear the filter and my original code can you add a line of code for visible so if I don't need it then I just comment

 

[Alex Blakenburg]

Let me know if you are using an Excel table in which case I need to modify this slightly otherwise here you go

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
    
        ' If filter applied remove filter
        If .FilterMode = True Then
            .ShowAllData
        End If
        
        lr = .Range("G" & .Rows.Count).End(xlUp).Row
        'lc = .Range("G" & .Columns.Count).End(xlToLeft).Column
        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]

Let me know if you are using an Excel table in which case I need to modify this slightly otherwise here you go

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
   
        ' If filter applied remove filter
        If .FilterMode = True Then
            .ShowAllData
        End If
       
        lr = .Range("G" & .Rows.Count).End(xlUp).Row
        'lc = .Range("G" & .Columns.Count).End(xlToLeft).Column
        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

Yes I use excel table

 

[Alex Blakenburg]

Without knowing the table name this is the easiest way. It should also mean it works whether or not it is a table.
Just make sure that whatever cell is selected is somewhere inside the table.

Just add this
sht.Range("G2").Select ' select a cell inside the table range
as shown below

        ' If filter applied remove filter
        sht.Range("G2").Select          ' select a cell inside the table range
        If .FilterMode = True Then
            .ShowAllData
        End If

 

[roykana]

Without knowing the table name this is the easiest way. It should also mean it works whether or not it is a table.
Just make sure that whatever cell is selected is somewhere inside the table.

Just add this
sht.Range("G2").Select ' select a cell inside the table range
as shown below

        ' If filter applied remove filter
        sht.Range("G2").Select          ' select a cell inside the table range
        If .FilterMode = True Then
            .ShowAllData
        End If

the code you provide runs perfectly and how is the vba code if using table names

 

[Alex Blakenburg]

how is the vba code if using table names

This is the format if you didn't want to select a cell in the table but wanted to use the table name

Sub ClearAllTableFilters()
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").AutoFilter.ShowAllData
End Sub

 

[roykana]

This is the format if you didn't want to select a cell in the table but wanted to use the table name

Sub ClearAllTableFilters()
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").AutoFilter.ShowAllData
End Sub

Thank you very much.

 

[Alex Blakenburg]

My pleasure