Auto - Filter (check on/off Status)

The function "Selection.Autofilter" can be used to add/remove an auto filter to a selected row. If a row has auto filters all ready on it, then using "Selection.Autofilter" removes these auto - filters. If a row does not have auto - filters then using "Selection.Autofilter" adds auto - filters to that row. Is then any way that I can run a "check" that will tell me if that row has auto-filters all ready set on it..?

Thanks a lot for the help.

asingh

Hi,

make good use of AutoFilterMode:

Code:

---

Option Explicit
Sub asingh()
    If ActiveSheet.AutoFilterMode Then
        MsgBox "AutoFilter: on"
    Else
        MsgBox "AutoFilter: off"
    End If
End Sub

---

Ciao,
Holger

Or you can also use

Selection.AutoFilter to turn it on and

ActiveSheet.ShowAllData to turn it off

Hi,

The option "If Activesheet.Autofiltermode ", seems to work perfect. I am able to trap if the auto filters are on or off.

When I tried the other method as suggested by Ha ha Ho Be, "Activesheet.Showalldata", I get the following run - time error "ShowAllData method of worksheet class failed". I made sure the filters were on when I tried this.

My purpose is solved using the IF condition, but I was still wondering if the ShowAllData method could be made to work, it does seem good to use..

thanks


asingh

Sorry about that...XLGibbs had suggested "ActiveSheet.ShowAllData" method....!

Generally speaking I find that the purpose of detecting whether the autofilter is on or not is because you want to apply a filter with only your criteria. I use something like the following:

Code:

---

    If ActiveSheet.AutoFilterMode Then
    ActiveSheet.ShowAllData
    End If

---

Or ...

Code:

---

    On Error Resume Next
    ActiveSheet.ShowAllData

---

Quote:

---

Originally Posted by asingh

Is then any way that I can run a "check" that will tell me if that row has auto-filters all ready set on it..?

asingh

---

I'm still curious if what he originally asked for is even possible....

I get an "ShowAllData method of worksheet class failed" error when trying to use the method "ActiveSheet.ShowAllData", even if the sheet has auto - filters set on it....?

Okay...both the methods are now working....to get "ActiveSheet.ShowAllData" to work, Auto - filters have to be on, and a filter critereon has to be set. I was not doing that. The auto - filters were on but the critereon was not defined.

thanks to all......

regards,

ASingh

So how do you establish if an auto filter criteria is in place ?

What i would like to do is to show all data if a filter is in place. I know i can achieve that if i switch off all filtering and recreate the filtering but I want to keep the existing auto filter. Also I don;t like doing it by allowing ShowAllData to error and ignoring the error - i just want to make sure that all rows are visible and there are no filtered out rows. Doing Activesheet.showalldata fails if a filter criteria is not selected.

Regards

Quote:

---

Originally Posted by sajina

Doing Activesheet.showalldata fails if a filter criteria is not selected.

---

... which is why you have the "On Error Resume Next"

• If the data is filtered and criteria is selected, the criteria is removed so all rows are displayed.
• If there is no autofilter on, all data is already displayed.
• If the filter is on but no criteria is selected, all data is already displayed.

So to restate, the simplest solution is

Code:

---

On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo ErrHandler

---