Convert Table to normal range, table name unknown

[Rowland Hamilton]

Folks:

How do I use this formula in excel vba...:

ActiveSheet.ListObjects("Table_Dyna_1").Unlist

...If I don't know the name of the table, or the table name will change?

Like maybe today the table name is "Table_Dyna_1" but later it could be "Table_Dyna_1_1" or "Table_Dyna_2" (probably because the original download is in the background or something). And I'm not sure of all possible variations on the table name.

I tried a wildcard asterix, but that didn't work:

ActiveSheet.ListObjects("Table_Dyna*").Unlist

I will perform the "convert to range" command on a freshly imported, selected and active table.

Thank you - Rowland

 

[P.Holko]

Lets say you have a 4 tables named "Table_Dyna_i" where i is a numerical digit form 1 to 4 then code will be:

For i = 1 To 4
    ActiveSheet.ListObjects("Table_Dyna_" & i).Unlist
Next i

I can be wrong but you must have some info about tables' name.

 

[Rowland Hamilton]

Thanks for your quick response. Doesn't work probably because only one of the tables in the array actually exists, we just don't which of the names is correct. Probably need to Dim a tablename and find it with this:

Sub FindAllTablesOnSheet()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range
        MsgBox "Table found: " & oLo.Name & ", " & oLo.Range.Address
    Next
End Sub

Just one table so don't know if I need loop but not sure how to run withoug the loop.

Rowland

 

[shg]

If there is only one, maybe just

ActiveSheet.ListObjects(1).Unlist

 

[Rowland Hamilton]

Thanks. I just solved it with all this complicated formula:

Sub FindAllTablesOnSheet()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Dim MyTable As Variant
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range
        'MsgBox "Table found: " & oLo.Name & ", " & oLo.Range.Address
        MyTable = oLo.Name
        'MsgBox MyTable
        oSh.ListObjects(MyTable).Unlist
    Next
End Sub

BUt yours is 1 line of code with no variable setting, I hope it works.
-Rowland

 

[P.Holko]

Thanks for your quick response. Doesn't work probably because only one of the tables in the array actually exists, we just don't which of the names is correct. Probably need to Dim a tablename and find it with this:

Sub FindAllTablesOnSheet()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range
        MsgBox "Table found: " & oLo.Name & ", " & oLo.Range.Address
    Next
End Sub

Just one table so don't know if I need loop but not sure how to run withoug the loop.

Rowland

How about this:

Sub FindAllTablesOnSheet()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range
        MsgBox "Table found: " & oLo.Name & ", " & oLo.Range.Address
        [COLOR="DarkOrchid"]ActiveSheet.ListObjects(oLo.Name).Unlist[/COLOR]
    Next
End Sub

 

[Rowland Hamilton]

shg: Awesome, it works. I only have 1 table so I just need your 1 command. Thank you -Rowland:

Sub table_convert()
ActiveSheet.ListObjects(1).Unlist
End Sub

 

[Rowland Hamilton]

P.Holko: You're right, just needed to use the variables already defined. If I need to convert all worksheet lists, I can use this:

Sub FindAllTablesOnSheetUnlistThem()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range
        'MsgBox "Table found: " & oLo.Name & ", " & oLo.Range.Address
        oSh.ListObjects(oLo.Name).Unlist
    Next
End Sub

 

[shg]

You're welcome. P.Holko's code is indeed more versatile.

 

[P.Holko]

if you want to reduce lines of code, try procedure below.
As probably know this code will works with worksheet with unknown number of lists

Sub FindAllTablesOnSheet()
    Dim oLo As ListObject
    For Each oLo In ActiveSheet.ListObjects
        ActiveSheet.ListObjects(oLo.Name).Unlist
    Next
End Sub

regards