Creating Excel Tables in Excel VBA

An Excel Table is used to manage a group of related data easier, you can turn a range of cells into a Microsoft Office Excel table (previously known as an Excel list). A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table.

Sub CreateTable()

    Dim ListObj As ListObject
    
    On Error Resume Next
    Set ListObj = ActiveSheet.ListObjects("MyData")
    On Error GoTo 0
    
    ' if table does not exist, then create it
    If ListObj Is Nothing Then
        Set ListObj = ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$F$10"), , xlYes)
        ListObj.Name = "MyData"
        
        'decorate table
        ListObj.TableStyle = "TableStyleLight2"
    End If

End Sub

This one is work in progress...

This code has been tested on Microsoft Word and Excel 2013.

download sample spreadsheet

    Option Explicit
    Dim ListObj As ListObject
    
    Private Sub cmdCreate_Click()

    On Error Resume Next
    Set ListObj = ActiveSheet.ListObjects("MyData")
    On Error GoTo 0
    
    ' if table does not exist, then create it
    If ListObj Is Nothing Then
        Set ListObj = ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$F$10"), , xlYes)
        ListObj.Name = "MyData"
        
        'decorate table
        ListObj.TableStyle = "TableStyleLight2"
    End If

End Sub

Private Sub cmdDeleteTable_Click()
 
    If ListObj.Active Then
        ListObj.Unlist
    End If

End Sub


Private Sub cmdInsertRow_Click()

    Dim ListObj As ListObject
    Dim ws As Worksheet
    Dim Als() As Variant
    Dim rng As Range

    Set ws = ActiveSheet

    ' Get reference to table
    Set ListObj = ws.ListObjects("MyData")

    If ListObj.InsertRowRange Is Nothing Then
        ' List already has data
        Set rng = ListObj.ListRows.Add.Range
    Else
        ' List is empty
        Set rng = ListObj.InsertRowRange
    End If

 End Sub

Private Sub cmdDeleteRow_Click()

End Sub