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.
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
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.
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.
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