vbaexcelexcel-2007

If statement for detecting autofilter, even when nothing has been filtered

I've had a search around for this but havn't found anything yet. To clarify, I'm looking for a simple if statement for applying an autofilter if none exists, or do nothing if there is one already in place.

I thought the solution would be:

If ActiveSheet.FilterMode = False Then
    Selection.AutoFilter
end if

but this only detects filtered data. If a filter is already in place and you execute this line...

Selection.AutoFilter

The filter vanishes, and my code has a bad day. Is there a simple solution to this?

Solution

  • You were close:

    If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter