How can I check to see if the autofilter is on before I launch my code?

TIA

Mark Ivey

Mark,

Don't check simply turn it off. If it isn't on it won't generate an error

Sub FilterOff()
Worksheets("sheet1").AutoFilterMode = False
End Sub

Mike

"Mark Ivey" wrote:

How can I check to see if the autofilter is on before I launch my code?

TIA

Mark Ivey

Do you mean you want to check if the worksheet has autofilter applied--arrows
are added.

Or do you mean you want to check if the autofilter has been used to hide rows?

Maybe you can pick out what you need from this:

Option Explicit
Sub testme()
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
If .AutoFilterMode = True Then
MsgBox "Arrows are visible"
If .FilterMode = True Then
MsgBox "and there's at least one filter applied"
Else
MsgBox "but no fields have a filter applied"
End If
Else
MsgBox "No filter arrows applied"
End If
End With
End Sub

Sometimes, it's easier to just remove any autofiltering and start from scratch.

You could use this to remove any arrows and show all the data:
Activesheet.autofilter.mode = false

And it won't hurt if no filter has been applied.

And you didn't ask, but if you want to show all the data, but keep the arrows:

With Activesheet
If .FilterMode Then
.ShowAllData
End If
End With




Mark Ivey wrote:

How can I check to see if the autofilter is on before I launch my code?

TIA

Mark Ivey

--

Dave Peterson

Thanks for the advice....

I will give it a shot.

Mark

"Mike H" wrote in message
...
Mark,

Don't check simply turn it off. If it isn't on it won't generate an error

Sub FilterOff()
Worksheets("sheet1").AutoFilterMode = False
End Sub

Mike

"Mark Ivey" wrote:

How can I check to see if the autofilter is on before I launch my code?

TIA

Mark Ivey

Really good advice...

What I really would like is to do something like this...

1. Check to see if an autofilter is used
2. If true, find out what field and filter is being used and keep them in
memory (temporarily).
3. Turn off autofilter
4. Run my macro
5. Turn the autofilter back on as it was before I started.

Any ideas on this one...

TIA,

Mark Ivey



"Dave Peterson" wrote in message
...
Do you mean you want to check if the worksheet has autofilter
applied--arrows
are added.

Or do you mean you want to check if the autofilter has been used to hide
rows?

Maybe you can pick out what you need from this:

Option Explicit
Sub testme()
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
If .AutoFilterMode = True Then
MsgBox "Arrows are visible"
If .FilterMode = True Then
MsgBox "and there's at least one filter applied"
Else
MsgBox "but no fields have a filter applied"
End If
Else
MsgBox "No filter arrows applied"
End If
End With
End Sub

Sometimes, it's easier to just remove any autofiltering and start from
scratch.

You could use this to remove any arrows and show all the data:
Activesheet.autofilter.mode = false

And it won't hurt if no filter has been applied.

And you didn't ask, but if you want to show all the data, but keep the
arrows:

With Activesheet
If .FilterMode Then
.ShowAllData
End If
End With




Mark Ivey wrote:

How can I check to see if the autofilter is on before I launch my code?

TIA

Mark Ivey

--

Dave Peterson

I saved this from a Tom Ogilvy post:
====================================

http://j-walk.com/ss/excel/usertips/tip044.htm

to get it to refresh:

=FilterCriteria(B5)&left(Subtotal(9,B5:B200),0)

this is one I wrote back in 2000

Here is a user defined function that will display the criteria in a cell:

Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function

=ShowFilter(B5)&left(Subtotal(9,B5:B200),0)

would show the filter for column 2

I usually put these functions in cells above the filter

==============
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Mark Ivey wrote:

Really good advice...

What I really would like is to do something like this...

1. Check to see if an autofilter is used
2. If true, find out what field and filter is being used and keep them in
memory (temporarily).
3. Turn off autofilter
4. Run my macro
5. Turn the autofilter back on as it was before I started.

Any ideas on this one...

TIA,

Mark Ivey

"Dave Peterson" wrote in message
...
Do you mean you want to check if the worksheet has autofilter
applied--arrows
are added.

Or do you mean you want to check if the autofilter has been used to hide
rows?

Maybe you can pick out what you need from this:

Option Explicit
Sub testme()
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
If .AutoFilterMode = True Then
MsgBox "Arrows are visible"
If .FilterMode = True Then
MsgBox "and there's at least one filter applied"
Else
MsgBox "but no fields have a filter applied"
End If
Else
MsgBox "No filter arrows applied"
End If
End With
End Sub

Sometimes, it's easier to just remove any autofiltering and start from
scratch.

You could use this to remove any arrows and show all the data:
Activesheet.autofilter.mode = false

And it won't hurt if no filter has been applied.

And you didn't ask, but if you want to show all the data, but keep the
arrows:

With Activesheet
If .FilterMode Then
.ShowAllData
End If
End With




Mark Ivey wrote:

How can I check to see if the autofilter is on before I launch my code?

TIA

Mark Ivey

--

Dave Peterson

--

Dave Peterson