VBA:Getting a list of available criteria using autofilter

[maxell]

I am new to the board but have spent a lot of time "living inside" Excel VBA code. I am trying to find VBA code that lists or "gets" the criteria for each column once autofilter is on.

Is there a VBA function that can do this? Is there a way to "loop" through each criteria and store in an array?

Mike

 

[Joe Was]

This is a slight modification to the HELP sample for the Object (you need to have active filters, that is you need to select a criteria from at least one AutoFilter to use this code):


Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String

Sub ChangeFilters()
Dim myKs

Set w = Worksheets("Sheet1")

With w.AutoFilter
currentFiltRange = .Range.Address

With .Filters
ReDim filterArray(1 To .Count, 1 To 3)

For f = 1 To .Count
With .Item(f)
myKs = myKs & "Filter: " & f & " "

If .On Then
filterArray(f, 1) = .Criteria1
myKs = myKs & "Criteria1: " & filterArray(f, 1) & " "

If .Operator Then
filterArray(f, 2) = .Operator
myKs = myKs & "Operator: " & filterArray(f, 2) & " "

filterArray(f, 3) = .Criteria2
myKs = myKs & "Criteria2: " & filterArray(f, 3) & " "

End If
End If
End With
myKs = myKs & " " & vbLf
Next f
End With
End With
MsgBox myKs

End Sub

 

[Tom Urtis]

Re: VBA:Getting a list of available criteria using autofilte

spent a lot of time "living inside" Excel VBA code.

Wow - - wonder if that has any long-term effects.

I am trying to find VBA code that lists or "gets" the criteria for each column once autofilter is on.

You may find this thread useful:
http://www.mrexcel.com/board2/viewtopic.php?t=158947

 

[maxell]

Re VBA:Getting a list of available criteria using autofilter

Thanks for the reply.

I was hoping for a way to identify the critier without having to go through and select each one.

The thread link you provided gives me some ideas.

I suspect I will have to loop through the actual list and copy unique entries to another list.

Thanks for the help, and yes - living inside excel does have long term effects - I always seem to want to select active sheets - lol

Mike