How To Use Or Delete A Table (ListObject)?

[CaliKidd]

I am using Excel 2007 and I created a table (i.e., listobject) using the macro recorder as follows:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$3:$J$50"), , xlYes).Name = "MyData"

Questions:
1. How do I delete this table via VBA?
2. How would I check to see if this table already exists so it follows this logic:

If "table does not exists" then
'create it (using the line of code above)
End If

 

[Domenic]

To delete a table and its data, try...

ActiveSheet.ListObjects("MyData").Delete

To delete a table without losing its data or table formatting, try...

ActiveSheet.ListObjects("MyData").Unlist

In the following code, if a table named "MyData" does not exist, it creates one...

Option Explicit

Sub test()

    Dim ListObj As ListObject
    
    On Error Resume Next
    Set ListObj = ActiveSheet.ListObjects("MyData")
    On Error GoTo 0
    
    If ListObj Is Nothing Then
        Set ListObj = ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$3:$J$50"), , xlYes)
        ListObj.Name = "MyData"
    End If

End Sub

 

[CaliKidd]

Thanks Domenic!

 

[Domenic]

You're very welcome!

 

[TrashmanL]

So, call me a stickler. #1. I don't like code that relies upon generating an error to work, and #2. I don't like magic "numbers" (or strings). So, if you're like me, use this code instead:

Option Explicit

Sub test()

    Dim ListObj As ListObject
    Dim MyName As String
    Dim MyRangeString As String
    Dim MyListExists As Boolean

    MyName = "MyData"
    MyRangeString = "$A$3:$J$50"

    MyListExists = False
    For Each ListObj In ActiveSheet.ListObjects
       
        If ListObj.Name = MyName Then MyListExists = True
        
    Next ListObj
    
    If Not(MyListExists) Then
        Set ListObj = ActiveSheet.ListObjects.Add(xlSrcRange, Range(MyRangeString), , xlYes)
        ListObj.Name = MyName
    End If

End Sub

This doesn't rely on an error being generated to detect it doesn't exist.
This also has the advantage that you can change the variable "MyName" to check for other tables, or even loop through a set of names.
Same with MyRangeString, if your range changes (better would be to calculate it somehow)