Obtaining AutoFilter Filter Settings Automatically

[mark]

Hello.

I have a worksheet with an autofilter set. The AutoFilter has 69 columns in
it. I can obtain that number like this:

Dim flt As AutoFilter
Set flt = wsBOM.AutoFilter
MsgBox flt.Filters.count

What I want to know is if one of two specific columns are filtered to =TRUE

I can find that, if they are, by using:

MsgBox flt.Filters(Range(cnLTMFGCPOrChange).Column).Criteria1 .

If the filter is set, that returns "=TRUE"

But, if the filter is not set to anything, I get an error message box which
has the title Microsoft Visual Basic, has a red circle x on the left, and
says 400.

I tried putting On Error Resume Next before it, but it still gives me the
error message box.

Can someone help me with how to return or pass by in code when a filter is
not set? Basically, the normal condition is that one of two filters will be
set. I need to be able to trap which one it was, and hold that information
to re-apply it at the end.

Thanks.
Mark

 

[mark]

think I got it... think I messed up the On Error statement

this seems to work:

Sub test()

Dim i As Integer
Dim flt As AutoFilter
Dim fltSet As String
Set flt = ActiveSheet.AutoFilter


For i = 1 To flt.Filters.Count Step 1
On Error Resume Next
fltSet = flt.Filters(i).Criteria1
On Error GoTo 0


If fltSet = "" Then

MsgBox "Filter Number " & i & " isn't set."

Else

MsgBox "Filter Number " & i & " is " & fltSet & "."

End If

fltSet = ""

Next i

End Sub

 

[OssieMac]

Hello Mark,

Hopefully the following example will point you in the right direction.

Sub TestForFilter()

'Note Criteria2 is if custom filter Between etc is used
'and .Operatior tests for this

Dim i As Long

i = 2 'No of col/filter being tested

With ActiveSheet
'Test if Autofilter invoked
If .AutoFilterMode Then
'Test if an actual filter is set
If .FilterMode Then
With .AutoFilter.Filters(i)
If .On Then
MsgBox "Filter " & i & " is " & .Criteria1
If .Operator Then
MsgBox "Filter " & i & " is " & .Criteria2
Else
MsgBox "No Criteria 2 for Filter " & i
End If
Else
MsgBox "Filter " & i & " is Off"
End If
End With
Else
MsgBox "No Filters have been set."
End If
Else
MsgBox "Autofilter is not turned on."
End If

End With

 

[mark]

I got ya... check to see if it the filter is on, before trying to read the
criteria.

got it.

thanks.