detecting the state of filters

[Chris Spencer]

I have a macro in Excel which needs to ensure that autofilters are on
a worksheet certain times and turned off at other times.

At the moment I am just changing the state of the filters with:

Sheets(1).Rows("1:1").AutoFilter

So if filters are on, then this command will turn them off and vice
versa. I need some kind of check to tell whether or not the filters
are on my sheet. Any suggestions.

Thanks in advance.
Chris

 

[keepitcool]

Chris, what you're looking for is

Worksheets(1).AutoFilterMode
cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Dave Peterson]

One more:

You can check if the worksheet has had filters applied. And you can even check
to see if any of the filters are in use:

With ActiveSheet
If .AutoFilterMode Then
MsgBox "Has dropdown Arrows!"
If .FilterMode Then
MsgBox "Some filter is active!"
End If
End If
End With

Or if you think that it might have been changed, you can just turn it off and
apply it where you want:

with activesheet
.autofiltermode = false
.range("w12:al12").autofilter
end with

Although I like choosing my whole range when I apply the autofilter.