check existence of autofilter, turn off autofilter

[Rowland Hamilton]

Folks:

Why can't I get any of these codes to work (I'm using Excel 2010)?:

Sub AutoFilOff()
    If ActiveProject.AutoFilter = True Then
        ActiveProject.AutoFilter = False
    End If
End Sub

Or this one?

Sub AutoFltrTgl()
ActiveProject.AutoFilter = Not ActiveProject.AutoFilter
End sub

Great if they worked because they are simple and it doesn't matter where the filter is in the worksheet.

Doesn't like "activeProject" but didn't work with "Active.Sheets" either

Now this did work, but how can I modify it to then turn off the filter? And why is it so much longer than the other ones?:

Sub Check_AutoFilter_IsPresent()
'Check if Sheet has AutoFilter
'Check if filter was applied to any column

Dim oWS As Worksheet ' Worksheet Object

On Error GoTo Disp_Error
 
oWS = ActiveSheet

If Not oWS.AutoFilter Is Nothing Then
If oWS.FilterMode = True Then
MsgBox ("Auto Filter On: Filter Mode On")
Else
MsgBox ("Auto Filter On: Filter Mode Off")
End If
Else
MsgBox ("Auto Filter Off")
End If

If Not oWS Is Nothing Then oWS = Nothing

' --------------------
' Error Handling
' --------------------
Disp_Error:
If Err <> 0 Then
MsgBox Err.Number & " - " & Err.Description, vbExclamation, "ERROR MESSAGE"
Resume Next
End If

End Sub

Thank you - Rowland

 

[Tom Urtis]

ActiveSheet.AutoFilterMode = False


should suffice

 

[Rowland Hamilton]

Actually, The third Macro only works first time then it always says the filter is on.

 

[Rowland Hamilton]

Thanks Tom:

I discovered that when I modified the following code I found on line (made the parts I didn't need text:

Sub Autofilter_example_50()
Dim ws As Worksheet
With Sheets("Data")
.AutoFilterMode = False
'.Cells.AutoFilter Field:=4, _
'Criteria1:=Sheets("Claims").Range("G2").Text
'.Cells.AutoFilter Field:=6, _
'Criteria1:=Sheets("Claims").Range("G3").Text
'Set ws = Worksheets.Add(Before:=Sheets("Data"))
'.UsedRange.Copy ws.Range("A1")
'.AutoFilterMode = False
End With
'Set ws = Nothing
End Sub

Rowland

 

[Rowland Hamilton]

Perfect, one line in code, thanks Tom