[Excel VBA] How to Get and List Current Filter Conditions (Complete Sample Code)

2025年12月29日

Overview

Sometimes you may want to check the AutoFilter criteria (Criteria1 / Criteria2) set on a worksheet for each column.

By running the macro introduced in this article, you can display a list of which conditions are set for which columns in a message box. Please use this for checking setting errors or for logging purposes.

Sample Code (VBA)

Sub DisplayFilterConditions()

    Dim ws          As Worksheet        ' 対象シート
    Dim iCol        As Long             ' ループ用カウンター
    Dim infoMsg     As String           ' 条件一覧を格納する文字列
    
    Set ws = ActiveSheet                ' 必要に応じてシート名を指定
    
    If ws.AutoFilterMode = False Then
        MsgBox "このシートにはフィルターが設定されていません。", vbExclamation, "Filter Info"
        Exit Sub
    End If
    
    With ws.AutoFilter
        For iCol = 1 To .Filters.Count
            With .Filters(iCol)
                If .On = True Then                           ' 抽出が有効か判定
                    infoMsg = infoMsg & _
                              "列 " & iCol & " の抽出条件 : "
                    
                    If .Operator <> 0 Then                   ' AND / OR 条件の場合
                        infoMsg = infoMsg & _
                                  .Criteria1 & " と " & .Criteria2 & vbCrLf
                    Else                                     ' 単一条件の場合
                        infoMsg = infoMsg & .Criteria1 & vbCrLf
                    End If
                End If
            End With
        Next iCol
    End With
    
    If Len(infoMsg) = 0 Then
        infoMsg = "現在、抽出条件は設定されていません。"
    End If
    
    MsgBox infoMsg, vbInformation, "Filter Info"

End Sub

Code Explanation

Line	Explanation
6	Sets the target sheet using `Set ws = ActiveSheet`. If you want to specify a fixed sheet, use `Worksheets("SheetName")`.
8–11	If `AutoFilterMode` is False, the filter row itself does not exist, so the process ends.
13	Loops through the `ws.AutoFilter` object to retrieve conditions for each column.
15	If `.On` is True, it means filtering is active for that column.
18–22	Checks `.Operator` and switches the message format depending on whether it is an AND/OR condition (multiple conditions) or a single condition.
27	Sets a message if no conditions are set on any column.

Application Tips

Export the list to a cellYou can output the list to a cell using code like Range(“G2”).Value = infoMsg.
Branch processing based on criteriaYou can check if specific conditions are met by comparing .Criteria1 or .Criteria2 as strings.
Notification for forgotten filtersYou can expand the code to automatically clear filters or show a warning message if any active filters are found.