I need a way to clear any and all AdvancedFilter on a worksheet so that all
rows are displayed. I've tried the ShowAllData method and it works fine as
long as there are filtered rows on the sheet. But the method fails if there
are no filtered rows.

Is there a way ShowAllData regardless of whether rows are filtered? If not,
is there a way to check for AdvancedFilter--a sort of AdvancedFilter property?

Many thanks.

Take a look at this:
http://www.contextures.com/xlautofilter03.html


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Ted M H" wrote:

> I need a way to clear any and all AdvancedFilter on a worksheet so that all
> rows are displayed. I've tried the ShowAllData method and it works fine as
> long as there are filtered rows on the sheet. But the method fails if there
> are no filtered rows.
>
> Is there a way ShowAllData regardless of whether rows are filtered? If not,
> is there a way to check for AdvancedFilter--a sort of AdvancedFilter property?
>
> Many thanks.
>
>
>

Hi
Just suppress the error

On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows

regards
Paul

On May 11, 3:55*pm, Ted M H <Te...@discussions.microsoft.com> wrote:
> I need a way to clear any and all AdvancedFilter on a worksheet so that all
> rows are displayed. *I've tried the ShowAllData method and it works fine as
> long as there are filtered rows on the sheet. *But the method fails if there
> are no filtered rows.
>
> Is there a way ShowAllData regardless of whether rows are filtered? *Ifnot,
> is there a way to check for AdvancedFilter--a sort of AdvancedFilter property?
>
> Many thanks.

In a project long ago, I had problems with ShowAllData, which I worked around
like this:

On Error Resume Next
'Unhide all rows first.
ActiveSheet.ShowAllData
'In case ShowAllData doesn't work, unhide all rows.
ActiveSheet.Cells.EntireRow.Hidden = False
On Error GoTo My_ErrorHandler

Hope this helps,

Hutch

"Ted M H" wrote:

> I need a way to clear any and all AdvancedFilter on a worksheet so that all
> rows are displayed. I've tried the ShowAllData method and it works fine as
> long as there are filtered rows on the sheet. But the method fails if there
> are no filtered rows.
>
> Is there a way ShowAllData regardless of whether rows are filtered? If not,
> is there a way to check for AdvancedFilter--a sort of AdvancedFilter property?
>
> Many thanks.
>
>
>

Thanks for the information. The autofilter stuff is mostly irrelevant to my
question, which is about advanced filter.

"ryguy7272" wrote:

> Take a look at this:
> http://www.contextures.com/xlautofilter03.html
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Ted M H" wrote:
>
> > I need a way to clear any and all AdvancedFilter on a worksheet so that all
> > rows are displayed. I've tried the ShowAllData method and it works fine as
> > long as there are filtered rows on the sheet. But the method fails if there
> > are no filtered rows.
> >
> > Is there a way ShowAllData regardless of whether rows are filtered? If not,
> > is there a way to check for AdvancedFilter--a sort of AdvancedFilter property?
> >
> > Many thanks.
> >
> >
> >

Thanks a million for the quick replies. I ended up going with the on error
resume next option. I didn't realize that the worksheet.filtermode property
applied to either autofilter or advanced filter. Now I know.
Much obliged.

"Ted M H" wrote:

> I need a way to clear any and all AdvancedFilter on a worksheet so that all
> rows are displayed. I've tried the ShowAllData method and it works fine as
> long as there are filtered rows on the sheet. But the method fails if there
> are no filtered rows.
>
> Is there a way ShowAllData regardless of whether rows are filtered? If not,
> is there a way to check for AdvancedFilter--a sort of AdvancedFilter property?
>
> Many thanks.
>
>
>

> ...If not, is there a way to check for AdvancedFilter...?

If you would have looked through the code samples at the link that Ryan
provided, I think you might have found the answer to the above question in
the "Turn On Excel AutoFilter" example.

--
Rick (MVP - Excel)



"Ted M H" <(E-Mail Removed)> wrote in message
news:F065ECAB-C6CD-4454-9571-(E-Mail Removed)...
> Thanks for the information. The autofilter stuff is mostly irrelevant to
> my
> question, which is about advanced filter.
>
> "ryguy7272" wrote:
>
>> Take a look at this:
>> http://www.contextures.com/xlautofilter03.html
>>
>>
>> --
>> Ryan---
>> If this information was helpful, please indicate this by clicking
>> ''Yes''.
>>
>>
>> "Ted M H" wrote:
>>
>> > I need a way to clear any and all AdvancedFilter on a worksheet so that
>> > all
>> > rows are displayed. I've tried the ShowAllData method and it works
>> > fine as
>> > long as there are filtered rows on the sheet. But the method fails if
>> > there
>> > are no filtered rows.
>> >
>> > Is there a way ShowAllData regardless of whether rows are filtered? If
>> > not,
>> > is there a way to check for AdvancedFilter--a sort of AdvancedFilter
>> > property?
>> >
>> > Many thanks.
>> >
>> >
>> >

Hi Rick,

I'm not sure what your point is, but I sure don't see the answer in these
code samples. The Turn on Excel AutoFilter example definitely does not
provide the answer. Again, my question was about Advanced Filter and the
AutoFilterMode property has nothing to do with Advanced Filter.

Thanks for your interest, though.

"Rick Rothstein" wrote:

> > ...If not, is there a way to check for AdvancedFilter...?
>
> If you would have looked through the code samples at the link that Ryan
> provided, I think you might have found the answer to the above question in
> the "Turn On Excel AutoFilter" example.
>
> --
> Rick (MVP - Excel)
>
>
>
> "Ted M H" <(E-Mail Removed)> wrote in message
> news:F065ECAB-C6CD-4454-9571-(E-Mail Removed)...
> > Thanks for the information. The autofilter stuff is mostly irrelevant to
> > my
> > question, which is about advanced filter.
> >
> > "ryguy7272" wrote:
> >
> >> Take a look at this:
> >> http://www.contextures.com/xlautofilter03.html
> >>
> >>
> >> --
> >> Ryan---
> >> If this information was helpful, please indicate this by clicking
> >> ''Yes''.
> >>
> >>
> >> "Ted M H" wrote:
> >>
> >> > I need a way to clear any and all AdvancedFilter on a worksheet so that
> >> > all
> >> > rows are displayed. I've tried the ShowAllData method and it works
> >> > fine as
> >> > long as there are filtered rows on the sheet. But the method fails if
> >> > there
> >> > are no filtered rows.
> >> >
> >> > Is there a way ShowAllData regardless of whether rows are filtered? If
> >> > not,
> >> > is there a way to check for AdvancedFilter--a sort of AdvancedFilter
> >> > property?
> >> >
> >> > Many thanks.
> >> >
> >> >
> >> >
> .
>

You can use;

Sub RemoveFilters()
On Error Resume Next
Sheet1.ShowAllData
On Error GoTo 0
End Sub



--
Regards
Dave Hawley
www.ozgrid.com
"Ted M H" <(E-Mail Removed)> wrote in message
news:E3A9BF2F-2B54-49D8-AB20-(E-Mail Removed)...
> Hi Rick,
>
> I'm not sure what your point is, but I sure don't see the answer in these
> code samples. The Turn on Excel AutoFilter example definitely does not
> provide the answer. Again, my question was about Advanced Filter and the
> AutoFilterMode property has nothing to do with Advanced Filter.
>
> Thanks for your interest, though.
>
> "Rick Rothstein" wrote:
>
>> > ...If not, is there a way to check for AdvancedFilter...?
>>
>> If you would have looked through the code samples at the link that Ryan
>> provided, I think you might have found the answer to the above question
>> in
>> the "Turn On Excel AutoFilter" example.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>>
>> "Ted M H" <(E-Mail Removed)> wrote in message
>> news:F065ECAB-C6CD-4454-9571-(E-Mail Removed)...
>> > Thanks for the information. The autofilter stuff is mostly irrelevant
>> > to
>> > my
>> > question, which is about advanced filter.
>> >
>> > "ryguy7272" wrote:
>> >
>> >> Take a look at this:
>> >> http://www.contextures.com/xlautofilter03.html
>> >>
>> >>
>> >> --
>> >> Ryan---
>> >> If this information was helpful, please indicate this by clicking
>> >> ''Yes''.
>> >>
>> >>
>> >> "Ted M H" wrote:
>> >>
>> >> > I need a way to clear any and all AdvancedFilter on a worksheet so
>> >> > that
>> >> > all
>> >> > rows are displayed. I've tried the ShowAllData method and it works
>> >> > fine as
>> >> > long as there are filtered rows on the sheet. But the method fails
>> >> > if
>> >> > there
>> >> > are no filtered rows.
>> >> >
>> >> > Is there a way ShowAllData regardless of whether rows are filtered?
>> >> > If
>> >> > not,
>> >> > is there a way to check for AdvancedFilter--a sort of AdvancedFilter
>> >> > property?
>> >> >
>> >> > Many thanks.
>> >> >
>> >> >
>> >> >
>> .
>>

Please accept my profuse apology... I read your reference to "Advanced
Filter" and my mind's eye saw "AutoFilter". I had just been doing some stuff
with AutoFilter which, I guess, conditioned me to think "Auto" when I saw
the word "Advanced"... very strange.

--
Rick (MVP - Excel)



"Ted M H" <(E-Mail Removed)> wrote in message
news:E3A9BF2F-2B54-49D8-AB20-(E-Mail Removed)...
> Hi Rick,
>
> I'm not sure what your point is, but I sure don't see the answer in these
> code samples. The Turn on Excel AutoFilter example definitely does not
> provide the answer. Again, my question was about Advanced Filter and the
> AutoFilterMode property has nothing to do with Advanced Filter.
>
> Thanks for your interest, though.
>
> "Rick Rothstein" wrote:
>
>> > ...If not, is there a way to check for AdvancedFilter...?
>>
>> If you would have looked through the code samples at the link that Ryan
>> provided, I think you might have found the answer to the above question
>> in
>> the "Turn On Excel AutoFilter" example.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>>
>> "Ted M H" <(E-Mail Removed)> wrote in message
>> news:F065ECAB-C6CD-4454-9571-(E-Mail Removed)...
>> > Thanks for the information. The autofilter stuff is mostly irrelevant
>> > to
>> > my
>> > question, which is about advanced filter.
>> >
>> > "ryguy7272" wrote:
>> >
>> >> Take a look at this:
>> >> http://www.contextures.com/xlautofilter03.html
>> >>
>> >>
>> >> --
>> >> Ryan---
>> >> If this information was helpful, please indicate this by clicking
>> >> ''Yes''.
>> >>
>> >>
>> >> "Ted M H" wrote:
>> >>
>> >> > I need a way to clear any and all AdvancedFilter on a worksheet so
>> >> > that
>> >> > all
>> >> > rows are displayed. I've tried the ShowAllData method and it works
>> >> > fine as
>> >> > long as there are filtered rows on the sheet. But the method fails
>> >> > if
>> >> > there
>> >> > are no filtered rows.
>> >> >
>> >> > Is there a way ShowAllData regardless of whether rows are filtered?
>> >> > If
>> >> > not,
>> >> > is there a way to check for AdvancedFilter--a sort of AdvancedFilter
>> >> > property?
>> >> >
>> >> > Many thanks.
>> >> >
>> >> >
>> >> >
>> .
>>