CODEWITHSUNDEEP
vbafiltercriteria
superadamwo
superadamwo

Reputation: 63

VBA: Loop through criteria in an inactive excel filter?

I'm looking for a way to loop through the criteria of an excel filter which doesn't have anything filtered out (ie. all the criteria are enabled). When the filter is active, all the criteria are added to an array-type thing which can be accessed with:

for each criteria in ActiveSheet.AutoFilter.Filters(filterColumn).Criteria1

However, when the filter is not activated, the Criteria1 and Criteria2 methods give me an error and don't seem to return anything. It seems silly that there wouldn't be a way to loop through all the criteria, but so far I haven't found it.

Upvotes: 2

Views: 2206

Answers (2)

Astha
Astha

Reputation: 1

See below logic

  1. it will first find the Count of filters in Activesheet.
  2. If filter is applied in particular column, it will count how many criteria is applied in that respective column
  3. Depending on Count , the Criteria name is retrived in variable crtnme
Set Sht = ActiveSheet
 With Sht.AutoFilter
  For i = 1 To .Filters.Count
     If .Filters(i).On Then
       Sheets("Filter").Cells(j, 1).Value = .Range(6, i).Column 'header starts from 6th row
       A = .Filters(i).Count
       If A = 1 Then
        crtnme = .Filters(i).Criteria1
        msgbox(crtnme)
       ElseIf A = 2 Then
        crtnme  = .Filters(i).Criteria1
        crtnme  = crtnme   & "|" & .Filters(i).Criteria2
        msgbox(crtnme)
       Else
        For k = 1 To A
         crtnme  = crtnme  & "|" & .Filters(i).Criteria1(k)
        Next
        msgbox(crtnme)
       End If
    End If
  Next i
  End With

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

When you have 1 or 2 criteria selected then you can simply retrieve them like this

Option Explicit

Sub sample()
    Dim rRange As Range
    Dim iFiltCrit As Long

    'Remove any filters
    ActiveSheet.AutoFilterMode = False

    Set rRange = Range("A1:B8")

    With rRange
        .AutoFilter Field:=1, Criteria1:="=1"

        Debug.Print Sheet1.AutoFilter.Filters(1).Criteria1
    End With

    'Remove any filters
    ActiveSheet.AutoFilterMode = False
End Sub

When you have more than 2 criteria then you can loop as you mentioned as they are stored in an array.

The problem is when the filter is inactive i.e You can see everything then all the criteria is cleared off from memory. See this example. I am using ActiveSheet.ShowAllData to show all the data.

Option Explicit

Sub sample()
    Dim rRange As Range

    'Remove any filters
    ActiveSheet.AutoFilterMode = False

    Set rRange = Range("A1:B8")

    With rRange
        .AutoFilter Field:=1, Criteria1:="=1"

        Debug.Print ">"; Sheet1.AutoFilter.Filters(1).Criteria1

        '~~> Show all data
        ActiveSheet.ShowAllData

        Debug.Print ">>"; Sheet1.AutoFilter.Filters(1).Criteria1
    End With

    'Remove any filters
    ActiveSheet.AutoFilterMode = False

End Sub

SNAPSHOT

enter image description here

So I believe that you cannot retrieve that information any more once you show all the data.

Upvotes: 1

Related Questions