VBA: Loop through criteria in an inactive excel filter?

Question

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.

Can you just just activecell.value ="" and then go to next cell?? — Andrew
– Andrew, Commented Apr 26, 2012 at 15:06
@superadamwo Wouldn't this simply be the same as getting the unique values in the particular column (which is a trivial problem)? — Zairja
– Zairja, Commented May 1, 2012 at 12:41

Siddharth Rout · Accepted Answer · 2012-04-26 15:15:23Z

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.

brasofilo · Accepted Answer · 2014-06-02 08:50:11Z

See below logic

it will first find the Count of filters in Activesheet.
If filter is applied in particular column, it will count how many criteria is applied in that respective column
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

Collectives™ on Stack Overflow

VBA: Loop through criteria in an inactive excel filter?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related