Check AutoFilter is On or Off in VBA

[maatman]

Hi,

I want to check if there is an AutoFilter on a worksheet or not with the following code:

Dim ReturnValue As AutoFilter
ReturnValue = ActiveSheet.AutoFilter
If ReturnValue Is Nothing Then MsgBox "There is no Filter"
If ReturnValue = ActiveSheet.AutoFilter then MsgBox "There is a Filter"

This code always returns "There is no Filter" even when there is an AutoFilter.
What is wrong?

Regards

 

[njimack]

If ActiveSheet.FilterMode Then
Msgbox "There is a Filter"
Else: Msgbox "There is no Filter
End If

 

[maatman]

Hi,

What I want is to detect if there is an AutoFilter. Not if the AutoFilter is in filtering mode or not.

Regards

 

[njimack]

If ActiveSheet.AutoFilterMode Then
MsgBox "There is a Filter"
Else: MsgBox "There is no Filter"
End If

End Sub

 

[maatman]

Hi,

Hm... you don't let me down Thanks!

How can I turn the AutoFilter off so it disappears.

Regards

 

[njimack]

ActiveSheet.AutoFilterMode = False

By the way, all of this code (and more) can be found at http://contextures.com/xlautofilter03.html

 

[maatman]

Thank you very much.
Once again I can please my boss thanks to the wizards of Mr.Excel

 

[Leroy88]

If ActiveSheet.AutoFilterMode Then
MsgBox "There is a Filter"
Else: MsgBox "There is no Filter"
End If

End Sub

Hello, I am trying this but with mixed success. When I write the VBA code to reference a sheet within the active workbook, the code works. My need is for VBA to open a different workbook and detect if autofilter mode is on in a sheet there. Once I have VBA open that workbook, and goto the specified worksheet, I used the following code:

Worksheets("worksheet").Activate
If ActiveSheet.AutoFilterMode Then
MsgBox "There is a Filter"
Else: MsgBox "There is no Filter"
Selection.AutoFilter
End If

Autofilter is on in the work sheet but the above code is not detecting it. Another piece of information which may or may not have any bearing, is that the sheet is a query output and the range size A1:CU10000. Does this have an impact?