[Excel VBA] How to Check the Filter Status of All Tables (ListObjects)

2025年12月26日

Overview

When you have multiple Tables (ListObjects) on a worksheet, you may want to instantly know if any of them have filters applied.

In this article, I will introduce a VBA macro that loops through all tables on a sheet, checks their AutoFilter status, and displays the result in a message box. Please use this as a check tool to prevent missing data or forgetting to clear filters.

Prerequisites

Item	Details
Excel Version	Microsoft 365 or 2016 and later
Target	All Tables (ListObjects) on the active sheet
Macro Location	Standard Module

Sample Code (VBA)

Sub CheckTableFilterStatus()

    Dim tbl As ListObject
    Dim msg As String
    
    ' Loop through all tables on the active sheet
    For Each tbl In ActiveSheet.ListObjects
        
        ' If AutoFilter is NOT Nothing, it means filter arrows exist
        If Not tbl.AutoFilter Is Nothing Then
            
            ' If FilterMode is True, data is currently being filtered
            If tbl.AutoFilter.FilterMode = True Then
                msg = tbl.Name & " - Filter criteria are currently applied."
            Else
                msg = tbl.Name & " - Filter arrows exist, but no data is filtered."
            End If
        
        Else
            msg = tbl.Name & " - No filter is set on this table."
        End If
        
        ' Display the status for each table
        MsgBox msg, vbInformation, "Filter Check"
        
    Next tbl

End Sub

Key Points of the Code

Line 6: We use the ListObjects collection to loop through every table on the worksheet.
Line 9: We check if AutoFilter is Nothing. If it is not Nothing, it means the filter arrows (dropdowns) are visible on the headers.
Line 12: If FilterMode is True, it means specific criteria are applied to one or more columns (rows are hidden).
Line 17: We use MsgBox to clearly notify the user of the status of each table.

Advanced Examples

Goal	Implementation Hint
List only filtered tables	Use `If tbl.AutoFilter.FilterMode Then ...` to filter the check, and output the name using `Debug.Print` or write it to a cell.
Automatically clear filters	Execute `tbl.Range.AutoFilter` if `FilterMode` is True to clear the criteria.
Check a specific sheet	Define the sheet using `Set ws = Worksheets("Data")` and loop through `ws.ListObjects`.

FAQ

Q: What happens if there are no tables on the sheet?

A: Since ListObjects.Count will be 0, the loop will not run, and no message will be displayed.

Q: How do I hide the filter arrows completely?

A: After clearing the filter, set tbl.ShowAutoFilter = False. This will remove the arrows from the headers.

Summary

By automatically checking the filter status of each table, you can easily verify your data and prevent operational errors.

First, try running the sample code to see the status of your tables displayed in the message box. You can customize the message content or the logic to build a more advanced check tool suited to your specific workflow.

よかったらシェアしてね！