Showing all in Filter
 

Apologies in advance because I know this is probably quite basic stuff
here...

I want to get a macro that releases all filters in all worksheets, but
I don't know how to get around the fact that a filter might not be
showing or set up for a particular sheet.

I'd appreciate any help to construct the necessary statement to get
around this.

thanks

tt


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=514843

Showing all in Filter
 

Each worksheet has two properties that will be of use here- FilterMode and
AutoFilterMode. The first of these indicates whether any rows are hidden by a
filter (either AutoFilter or Advanced Filter), the second indicates whether
the AutoFilter mode is active for a sheet, but will still = TRUE if all rows
are visible (i.e. AutoFilterMode only indicates whether the AutoFilter
dropdowns are visible on a worksheet). Note also that FilterMode is read
only, whereas AutoFilterMode is read/write (and can therefore be used to turn
off autofilter mode).

There is also a handy worksheet method ShowAllData, which will make all rows
visible, but will not remove the AutoFilter dropdowns. This method will raise
an error if there are no hidden rows.

Put all this together and you get something like this:
Sub RemoveAllFilters()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.FilterMode Then
ws.ShowAllData
End If
ws.AutoFilterMode = False
Next ws
End Sub

Cheers
Dave


"Timmy Mac1" wrote:


Apologies in advance because I know this is probably quite basic stuff
here...

I want to get a macro that releases all filters in all worksheets, but
I don't know how to get around the fact that a filter might not be
showing or set up for a particular sheet.

I'd appreciate any help to construct the necessary statement to get
around this.

thanks

tt


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=514843

Showing all in Filter
 

Sub bbb()
For Each sh In Worksheets
If sh.FilterMode Then
sh.ShowAllData
End If
Next
End Sub

is possibly what you want.

--
Regards,
Tom Ogilvy


"Timmy Mac1" wrote
in message ...

Apologies in advance because I know this is probably quite basic stuff
here...

I want to get a macro that releases all filters in all worksheets, but
I don't know how to get around the fact that a filter might not be
showing or set up for a particular sheet.

I'd appreciate any help to construct the necessary statement to get
around this.

thanks

tt


--
Timmy Mac1
------------------------------------------------------------------------
Timmy Mac1's Profile:
http://www.excelforum.com/member.php...o&userid=15188
View this thread: http://www.excelforum.com/showthread...hreadid=514843

Showing all in Filter
 

Tom, Dave

Many thanks for your helpful responses :)

cheers

t

--
Timmy Mac
-----------------------------------------------------------------------
Timmy Mac1's Profile: http://www.excelforum.com/member.php...fo&userid=1518
View this thread: http://www.excelforum.com/showthread.php?threadid=51484