VBA Delete Table from Worksheet & Workbook |Tutorial

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.

Table of Formats:

Objective
Syntax to Delet Table using VBA in Excel VBA
Example to delete Table on the Worksheet in Excel
Delete all Tables on the Worksheet in Excel
All Tables Delete from the WorkBook in Excel
Instructions to Run VBA Macro Code
Other Useful Resources

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