[Excel VBA] How to Check Filter Status of a Sheet | Notify if Data is Filtered

2025年12月27日

Overview

Determining whether an AutoFilter is set on a worksheet and whether filter criteria are currently applied allows for easier process branching and user alerts.

In this article, I will explain a sample macro that checks the current filter status by combining two properties: AutoFilterMode and FilterMode.

Prerequisites

Item	Details
Compatible Excel	Microsoft 365 or 2016 and later
Target Worksheet	Active Sheet (Can specify sheet name if needed)
Macro Location	Standard Module

Sample Code (VBA)

Sub CheckFilterStatus()

    Dim ws As Worksheet         ' Target sheet for checking
    Set ws = ActiveSheet        ' Change to Worksheets("Sheet1") if necessary
    
    If ws.AutoFilterMode = True Then                ' Filter arrows exist (AutoFilter is on)
        If ws.FilterMode = True Then                ' Filter criteria are currently applied
            MsgBox "Data is currently filtered.", vbInformation
        Else
            MsgBox "Filter is enabled, but no criteria are applied.", vbInformation
        End If
    Else
        MsgBox "No filter is set on this sheet.", vbExclamation
    End If

End Sub

Code Explanation

Line	Description
4	`Set ws = ActiveSheet` sets the active sheet as the target. To fix the target to a specific sheet, specify the sheet name here.
6	If `AutoFilterMode` is True, it means the filter arrows (drop-down buttons) are displayed in the header row.
7	If `FilterMode` is True, it means one or more filter conditions are set, and rows are currently being filtered (hidden).
8–12	Displays a message box based on the determination result to inform the user of the status.

Application Examples

Execute process only when filtered:Limit the condition using If ws.FilterMode Then ….
Auto-set filter if missing:Add an execution line like If Not ws.AutoFilterMode Then ws.Range(“A1”).AutoFilter.
Check without displaying messages:Convert logic into a function that returns a Boolean value, allowing it to be used for conditional branching without showing a message box.

FAQ

Q: Can this be used for Tables (ListObject)?

A: Yes. Since tables internally hold an AutoFilter, you can often determine the status using the same properties on the sheet (if it’s the active filter context), or by accessing the ListObject’s AutoFilter properties directly.

Q: How do I check multiple sheets at once?

A: You can loop through them using For Each ws In Worksheets and apply the logic above to each sheet.

Summary

By combining AutoFilterMode and FilterMode, you can concisely determine both the existence of the filter itself and its extraction status.

First, run the sample code to confirm that the appropriate message is displayed according to the filter settings. Incorporating this into business macros helps prevent processing errors caused by unexpected filter states.

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