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

Question

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?

Slatternly

Answer 1

You were close:

If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter

Sabir

Answer 2

There is a difference between .AutoFilterMode and .FilterMode.

.AutoFilterMode becomes True if the AutoFilter drop-down arrows are currently displayed on the worksheet.

.FilterMode becomes True only after the drop-down arrows are used to create a filter, or after filters are created in VBA code.

And so .AutoFilterMode must become true before .FilterMode.

Faulkner

that clears up a lot thank you – Slatternly