VBA Check if table Exists in Excel |Tutorial |Examples

VBA Check if table Exists in Excel. Let us check if a table exists on the worksheet. And also check if multiple tables are exist on the Sheet. We use ListObjects collection. In this tutorial we have explained multiple examples with explanation. We also shown example output screenshots. You can change table and sheet name as per your requirement. We also specified step by step instructions how to run VBA macro code at the end of the session.

Table of Formats:

Objective
Example to to Check If a table Exists on the Worksheet in Excel using VBA
Check Multiple Tables are exists on the Worksheet in Excel Workbook
Instructions to Run VBA Macro Code
Other Useful Resources

Example to to Check If a table Exists on the Worksheet

Let us see the example to check if a table Exists on the Worksheet using VBA. The sheet name defined as ‘Table‘. And we use table name as ‘MyTable1‘. You can change these two as per your requirement.
Where ListObjects represents the collection.

'VBA Check if table Exists
Sub VBAF1_Check_If_Table_Exists()

   'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    
    'Define Variable
    sTableName = "MyTable1"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
    
    'Loop through all tables in the worksheet
    For Each loTable In oSheetName.ListObjects
        'Check specified table
        If loTable.Name = sTableName Then
            'If exists
            MsgBox "Specified table is available.", vbInformation, "VBAF1"
        End If
    Next
    
End Sub

Output: Here is the following output screenshot of above example macro VBA code.

Check Multiple Tables are exists on the Worksheet

Here is the another example to check if multiple tables are exists on the Worksheet using VBA.

'VBA Check if multiple tables Exists on the worksheet
Sub VBAF1_Check_If_Multiple_Tables_Exists()

   'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    Dim bCheck As Boolean
        
    'Define WorkSheet object
    Set oSheetName = Sheets("Table")
       
    'Loop through all table names
    For iCnt = 2 To 7
        'Get Table Name
        sTableName = Sheets("Result").Range("A" & iCnt)
        bCheck = False
        
        'Loop through all tables in the worksheet
        For Each loTable In oSheetName.ListObjects
            'Check specified table
            If loTable.Name = sTableName Then
                'If exists
                Sheets("Result").Range("B" & iCnt) = "Available"
                bCheck = True
                Exit For
            End If
        Next
        
        'If table doesn't exists
        If bCheck = False Then
            Sheets("Result").Range("B" & iCnt) = " Not Available"
        End If
    Next
End Sub

Output: Let us see the following output screenshot of above example macro VBA code. The difference between before and after macro, see the above output screenshot.