autofilter turns toggles on/off

[bob]

the following adds autofilter to a protected sheet but the autofilter turns
off if it is already there. what statement is required bypass setting
autofilter if it is already on
thanks

' add autofilter back
Range("W12:AL12").Select
Selection.AutoFilter

're protect test sheet but allow filter
ActiveSheet.EnableAutoFilter = True
'ActiveSheet.EnableOutlining = True '///////
ActiveSheet.Protect Contents:=True, userInterfaceOnly:=True

 

[Dave Peterson]

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

Option Explicit
Sub testme()

With ActiveSheet
.AutoFilterMode = False
.Range("w12:al" & .Cells(.Rows.Count, "w").End(xlUp).Row).AutoFilter
End With
End Sub

(If my data has rows that are completely empty, this extends the autofilter to
where I want it to end. In my example, I used the last used row in column W.)