Prev: FOR EACH NEXT Multithreaded !
Next: require cell input before macro can run
From: Ted M H on 11 May 2010 10:55
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.



From: Paul Robinson on 11 May 2010 11:23
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...(a)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?  If not,
> is there a way to check for AdvancedFilter--a sort of AdvancedFilter property?
>
> Many thanks.

From: ryguy7272 on 11 May 2010 11:23
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.
>
>
>
From: Tom Hutchins on 11 May 2010 11:26
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.
>
>
>
From: aflatoon on 11 May 2010 11:56

You can check the worksheet's FilterMode property - if it's True then
ShowAllData.



T
e
d

M

H
;
7
2
1
2
7
9

W
r
o
t
e
:


>
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.


--
aflatoon

Regards,
A.
------------------------------------------------------------------------
aflatoon's Profile: http://www.thecodecage.com/forumz/member.php?u=1501
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=201955

http://www.thecodecage.com/forumz

 |  Next  |  Last
Pages: 1 2 3
Prev: FOR EACH NEXT Multithreaded !
Next: require cell input before macro can run