I am using Excel 2019 VBA.

It is possible to completly remove a table and more importantly a table name to make it available for immediate reuse via VBA without saving, closing and reopening a workbook?

To put it another way, at what point can a table name be reused after the original table has been removed and all connections, querys and pivot tables no longer show as existing in Excel?

If I enter any data in a worksheet, select that data as a range and then convert the range to a table, then later convert the table back to a range, clear all querys and connections and even delete the worksheet that the table was on, when I create a new table on a new worksheet and then try to use the same table name that I used before I receive a “Run-time error ‘1004’: A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table“.

However, there are no other ranges, query results, PivotTable reports, protected cells or any other tables any where in the workbook. Just a single worksheet and the only way Excel will allow me to reuse the table name is if I save, close and reopen the workbook.

​I am using Excel 2019 VBA. It is possible to completly remove a table and more importantly a table name to make it available for immediate reuse via VBA without saving, closing and reopening a workbook? To put it another way, at what point can a table name be reused after the original table has been removed and all connections, querys and pivot tables no longer show as existing in Excel? If I enter any data in a worksheet, select that data as a range and then convert the range to a table, then later convert the table back to a range, clear all querys and connections and even delete the worksheet that the table was on, when I create a new table on a new worksheet and then try to use the same table name that I used before I receive a “Run-time error ‘1004’: A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table”. However, there are no other ranges, query results, PivotTable reports, protected cells or any other tables any where in the workbook. Just a single worksheet and the only way Excel will allow me to reuse the table name is if I save, close and reopen the workbook.  Read More

​