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.

VBA Tables Tables Examples