VBA and Autofilters

  • Thread starter Thread starter Frank Haverkamp
  • Start date Start date
F

Frank Haverkamp

I want to know what the autofilter was last set to.
Below is a snipet of code.
For simplicity sake, lets just say I want to know what "Criteria1" was
last set on. Range("E118") is the autofilter.

Dim Count as String
Selection.AutoFilter Field:=5, Criteria1:="T&D Toolmakers"
Count = Range("E118").Value
MsgBox (Count)

It's not working, Help!
 
Frank,

This code dumps all the active filter criteria for Sheet1:

Sub testit()
Dim flt As Filter

For Each flt In Sheet1.AutoFilter.Filters
If flt.On Then
If flt.Operator = 0 Then
Debug.Print flt.Criteria1
Else
Debug.Print flt.Criteria1 & flt.Criteria2
End If
End If
Next
End Sub

Rob
 
If you want just one particular filter criteria do this:

Set w = Worksheets("Sheet1")
If w.AutoFilterMode Then
Criteriais = w.AutoFilter.Filters.Item(2).Criteria1
End If

Item(2) would be the 2nd filter from the left, Item(3) the 3rd and so on...

Mike
 
Back
Top