VBA Delete Table in Excel
VBA Delete Table from Worksheet & Workbook in Excel. This means deleting or removing the table on Worksheet in Excel using VBA. Here we use the ListObjects collection and Delete method to delete table. And also see how to delete multiple tables on worksheet and active WorkBook. In this tutorial let us learn the example and step by step instructions to delete table on the worksheet.
Syntax to Delete Table using VBA in Excel
Here is the syntax to delete table on the worksheet using VBA in Excel.
Expression.ListObjects(TableName).Delete
Example to Delete Table on the Worksheet in Excel VBA
Let us see the example to delete or remove table on the worksheet. The sheet name defined as ‘Table‘. And we use table name as ‘MyDynamicTable‘. You can change these two as per your requirement. Where Delete method is used to delete table on Worksheet.
'Delete Table using VBA in Excel Sub VBAF1_Delete_Table() 'Declare Variables Dim sTableName As String Dim sSheetName As String 'Define Variables sSheetName = "Table" sTableName = "MyDynamicTable" 'Delete Table Sheets(sSheetName).ListObjects(sTableName).Delete End Sub
Output: The table is deleted on the Worksheet.
Delete all Tables on the Worksheet in Excel
The following VBA code helps to delete all tables on the worksheet. The below code loop through all tables to delete on the worksheet.
'VBA Delete all Tables on Worksheet Sub VBAF1_Delete_All_Tables_On_Worksheet() 'Declare Variables Dim loTable As ListObject Dim sTableName As String Dim oSheetNameAs Worksheet 'Define Variables oSheetName= "MyDynamicTable" 'Set Sheet object Set sSheetName = Sheets("Table") 'Loop through each table in worksheet For Each loTable In oSheetName.ListObjects 'Define Table Name sTableName = loTable.Name 'Delete Table oSheetName.ListObjects(sTableName).Delete Next loTable End Sub
Output: The above code deleted all tables on the worksheet named Table.
All Tables Delete from the WorkBook in Excel
Let us see how to delete all tables delete from the WorkBook in Excel using VBA. The below code loop through all workbooks to delete tables from all sheets.
'VBA Delete all Tables from Workbook Sub VBAF1_Delete_All_Tables_from_Workbook() 'Declare Variables Dim loTable As ListObject Dim sTableName As String Dim oSheetName As Worksheet 'Define Variables sTableName = "MyDynamicTable" 'Set Sheet object Set oSheetName = Sheets("Table") 'Loop through each table in worksheet For Each oSheetName In ActiveWorkbook.Worksheets 'Loop through each table in worksheet For Each loTable In oSheetName.ListObjects 'Define Table Name sTableName = loTable.Name 'Delete Table oSheetName.ListObjects(sTableName).Delete Next loTable Next oSheetName End Sub
Instructions to Use Example Macro Code
You can follow step by step instructions to run above macro example VBA code.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window in the visual basic editor(VBE)
- Run macro by clicking F5 or Run command
- The defined table is deleted from the Workbook.
Other Related References
Click on the following links for related reference articles. It helps for your reference.