view · edit · history · print

Excel Auto Filter Visibility

why & what?

  • more visible then the simple small button icon
  • indicator in other fields
  • indicator using conditional formatting
  • indication using formula without VBA
  • indication of invisible rows, hidden rows

methods

  • Formula with counter using COUNTA & SUBTOTAL (no VBA, see sample in DispCriteria comments, may not be able to identify individual column filters)
  • VBA Function DispCriteria, Display Filter Criteria (least reliable)
  • VBA Function GetColumns, Get Column letters having a filter, else return "*" (good for single field overview)
  • VBA Function IsFiltered, if column Is Filtered return "!", else "*" (good for conditional format in auto filter header)

Function DispCriteria(Rng As Range) As String
    ' Display Filter Criteria

    ' parameter: Range, select applicable column for dynamic update
    ' sample: =DispCriteria(J:J)
    ' result: ">50" or "=TRUE" (good for booleans or small number filters)
    ' issue: criteria are not always picked-up completely

    ' note:
        'if you just want a count of filtered items you can better use a formula
        'sample: =COUNTA(I$2:I$22)-SUBTOTAL(103,I$2:I$22)

    Dim Filter As String

    Filter = ""
    On Error GoTo Done
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Done
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Done
            Filter = .Criteria1
            'criteria may be empty
            Select Case .Operator
                Case xlAnd
                    Filter = Filter & " AND " & .Criteria2
                Case xlOr
                    Filter = Filter & " OR " & .Criteria2
            End Select
        End With
    End With
Done:
    'criteria are not always picked-up completely
    If Filter = "" Then
         Filter = "?"
    End If
    DispCriteria = Filter
End Function

Function GetColumns(Rng As Range) As String
    ' Get Column letters having a filter, else return "*"

    ' parameter: Range, select first filtered column for dynamic update
    ' sample: =GetColumns(A:A)
    ' result: ,E,H,J,AA (=4 columns have filters)

    Dim Sht As Worksheet
    Dim i As Long
    Dim FList As String
    Dim ColumnNumber As Long
    Dim ColumnLetter As String

    FList = ""
    Set Sht = ActiveSheet

    With Sht.AutoFilter
       For i = 1 To .Filters.Count
          If .Filters(i).On Then
             ColumnNumber = .Range(1, i).Column
             ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)
             FList = FList & "," & ColumnLetter
          End If
       Next i
    End With
    If FList = "" Then
         FList = "*"
    End If
    GetColumns = FList
End Function

Function IsFiltered(Rng As Range) As String
    ' if column Is Filtered return "!", else "*"

    ' parameter: Range, select applicable column for dynamic update
    ' advantage: can also be used in conditional format using formula...
    ' sample: =IsFiltered(C:C)="!"

    Dim Filter As String

    Filter = ""
    On Error GoTo Done
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Done
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Done
            Filter = "!"
        End With
    End With
Done:
    If Filter = "" Then
         Filter = "*"
    End If
    IsFiltered = Filter
End Function

admin · attr · attach · edit · history · print
Page last modified on January 03, 2019, at 05:12 PM