Excel Create or Remove a Table with VBA

There are times in Excel when you may wish to create a table on the fly with the assistance of VBA code. I was in this position recently and needed to this with VBA. A file was uploaded into a sheet and the task was to create a table and then use that table for more data manipulation. The following Excel VBA procedure was what I ended up with.

Option Explicit

Sub NewTable() 'Excel VBA to create a table if there is none.
Dim ListObj As ListObject
Dim sTable As String

sTable = "DataTable"

   Set ListObj = ActiveSheet.ListObjects.Add(xlSrcRange, [A1].CurrentRegion, , xlYes)
   ListObj.Name = sTable 'The name for the table

End Sub

To complicate the procedure there was also a possibility that a table had been created by an end user, so I needed to test for the table in which case the code needed to become more complex. The following procedure will remove a table but will keep the formatting of the table so it looks like a table but performs like a normal Excel range.

Sub RemTable() 'Remove table, keep table format.
      Sheet1.ListObjects(1) .Unlist
End Sub

While the following will remove the table and the formatting of the table, so it is just like a worksheet with regular data.

Sub RemTableandFormat() 'Using the Activesheet for variety.
      ActiveSheet.ListObjects("MyData").Unlist
      [A1].CurrentRegion.ClearFormats
End Sub

In both procedures the ListObjects(1) assumes that on sheet1 (the worksheet code name not the sheet name) there is 1 table.

If you are trying to trap the name of a table the following might come in handy. WOrking with the name of the table after it has been created can help with referencing the table at a later point.

Sub TableName() 'Assign the table name to a string
Dim strName As String

strName = Sheet1.ListObjects(1).Name
End Sub