VBA Check If table Exists in Excel

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.

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.

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 difference between before and after macro, see the above output screenshot.

Other Related References

Click on the following links for related reference articles. It helps for your reference.

VBA Tables Tables Examples