VBA & Macro Problems

How to Delete an Excel Table and Reuse Its Name with VBA

Steve KSteve K Nov 11, 2026 868 views

Question details

The user needs a method to delete an existing table using a VBA macro and immediately assign that same name to a newly created table without encountering naming conflicts.

How to Delete an Excel Table and Reuse Its Name with VBA
Product
Excel
Device & OS
not provided
Scenario
Writing a VBA script to dynamically rebuild tables, which requires deleting an old table and recreating a new one with the exact same name.
Observed behavior
Excel often retains the table name in its internal metadata even after the table is converted to a range or deleted, triggering a 'name already exists' error when the macro tries to reuse it.
Before you start

Before running any VBA macros that delete tables or manipulate data structures, always create a backup copy of your workbook to prevent accidental data loss.

Solution 1Recommended

Use VBA to Delete the ListObject and Reassign the Name

Properly remove the table object from the worksheet's ListObjects collection before creating the new table to free up the name.

Excel table names are directly tied to `ListObject` objects rather than standard workbook named ranges. To reuse a table name, you must completely remove the existing `ListObject` using the `.Delete` or `.Unlist` methods.

If you only clear the cell contents, Excel still remembers the table name, causing runtime errors when you attempt to assign that name to your newly created table.

1
Open the VBA Editor

Press `ALT + F11` to open the Visual Basic for Applications (VBA) editor in your workbook.

2
Insert a New Module

Click on `Insert > Module` from the top menu to create a blank workspace for your macro.

3
Write the Deletion Script

Set your worksheet and table variables. Use `ws.ListObjects("YourTableName").Delete` to completely remove the old table.

4
Create and Rename the New Table

Use `ws.ListObjects.Add` to define the new table's range, then immediately set `newTbl.Name = "YourTableName"` to reuse the deleted name.

5
Run the Macro

Press `F5` while inside the module to execute the code and safely replace the table.

Preserving Data vs Deleting: If you want to keep the data but remove the table formatting and name, use `tbl.Unlist` instead of `tbl.Delete` in your VBA script.
Advanced VBA Support in WPS Office

Automate Table Management with WPS Spreadsheet

WPS Office provides robust built-in support for VBA and macros, allowing you to manipulate ListObjects, automate table creation, and reuse names effortlessly within a highly compatible interface.

  1. 1. Install WPS Office: Download and install the free version of WPS Office from the official website.
  2. 2. Open Your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
  3. 3. Enable Macros: Click 'Enable Macros' when prompted by the security warning at the top of the workspace.
  4. 4. Execute VBA Code: Press `ALT + F11` to open the WPS VBA Editor, paste your table management script, and press `F5` to run it.
Fully compatible with Microsoft Excel VBA scripts and macros, seamlessly supporting .xlsm formats.Includes a built-in VBA editor for writing, debugging, and running ListObject operations to manage tables.Lightweight, fast, and free to use for your daily spreadsheet automation and data management tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel say my table name already exists when I deleted the table?

This happens if you only cleared the table's data or deleted the rows instead of deleting the actual ListObject. It can also occur if the name is lingering in a hidden worksheet, PivotTable, Name Manager, or Power Query connection.

What is the difference between ListObject.Delete and ListObject.Unlist in VBA?

ListObject.Delete removes both the table formatting and the data completely. ListObject.Unlist converts the table back into a normal range of cells, keeping the data intact while removing the table properties and freeing up the table name.

Can I reuse a table name without restarting Excel?

Yes, properly deleting the ListObject via VBA usually frees the name immediately. However, if Excel experiences a caching glitch with its metadata, saving the file and reopening it is the most reliable way to clear the cache.

How do I find hidden tables using the same name?

Open the Name Manager under the Formulas tab to view all named ranges and tables. If a table exists on a hidden sheet, you will need to right-click a sheet tab, select Unhide, and reveal the hidden sheet first to delete the table object.