Delete table name in Excel VBA

Question

I would like to remove/delete the name of my table which is "Table2" using Excel VBA. I know how to remove the name by hand, but I cannot figure out how to set the name "Table2" as a name in VBA. I found other questions on this topic but those codes deleted all named ranges and I would like to remove just the table named "Table2".

This is my code that is not working:

Sub Delete_Name_Table()

   Dim n As Name

   n = "Table2"

   n.Delete

End Sub

Anyone who knows how to set n correctly to "Table2"?

Thanks!

Answer 1

You can backup data from table, delete table and restore data.

Sub test()
    Dim rng As Range
    Dim rngVals As Variant

    Set rng = YourSheet.ListObjects("Table2").Range
    rngVals = rng.Value

    YourSheet.ListObjects("Table2").Delete

    rng.Value = rngVals

    Set rng = Nothing
End Sub