オートフィルタ状態の確認

またも深夜のVBA探索。。。

フィルタ状況を調べる

Sub フィルタチェック()
    Dim i As Long, title As String
    Dim f As Filter
    Dim af As AutoFilter

    If ActiveSheet.AutoFilterMode Then
        For i = 1 To ActiveSheet.AutoFilter.Filters.Count
            Set f = ActiveSheet.AutoFilter.Filters(i)
            With f
                If f.On Then
                    title = ActiveSheet.AutoFilter.Range.Cells(1, i)
                    Debug.Print f.Operator
                    Select Case f.Operator
                    Case 0
                        Debug.Print title & f.Criteria1 & " で絞り込まれています"
                    Case xlAnd
                        Debug.Print title & f.Criteria1 & " and " & title & f.Criteria2 & " で絞り込まれています"
                    Case xlOr
                        Debug.Print title & f.Criteria1 & " or " & title & f.Criteria2 & " で絞り込まれています"
                    Case xlFilterValues
                        Dim j As Long
                        For j = LBound(f.Criteria1) To UBound(f.Criteria1)
                            Debug.Print title & f.Criteria1(j) & " で絞り込まれています"
                        Next j
                    Case Else
                    End Select
                End If
            End With
        Next i
        Debug.Print "レコード数：" & ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
    End If
End Sub

フィルタをかける

    ActiveSheet.AutoFilter.Range.AutoFilter Field:=3, Criteria1:="=00001", _
        Operator:=xlOr, Criteria2:="=00002"