Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / June 2006

Tip: Looking for answers? Try searching our database.

Excel should have a drop down box that shows all the filters on

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
dasmjp - 20 Jun 2006 19:04 GMT
We use large Excel files with filters.  I waste time scrolling across columns
to make sure what filters are still on or off.  Is there a feature in Excel
where you can
look to see what columns have filters on them?
Elkar - 20 Jun 2006 21:42 GMT
Unfortunately, the only built-in feature Excel offers is a nearly
imperceptible color change of the filter arrow from black to dark blue.  
Unless you've got great eye-sight, this probably isn't very helpful.

Here's a User-Defined Function that I got from this forum that I use on most
of my sheets that have lots of AutoFilters.  Create a module for your
workbook, then copy this code into it.

Function FilterCriteria(Rng As Range) As String
   Dim Filter As String
   Filter = ""
   On Error GoTo Finish
   With Rng.Parent.AutoFilter
       If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
       With .Filters(Rng.Column - .Range.Column + 1)
           If Not .On Then GoTo Finish
           Filter = .Criteria1
           Select Case .Operator
               Case xlAnd
                   Filter = Filter & " AND " & .Criteria2
               Case xlOr
                   Filter = Filter & " OR " & .Criteria2
           End Select
       End With
   End With
Finish:
   FilterCriteria = Filter
End Function

I then insert a new row above my Filter heading and use this formula:

=IF(filtercriteria(A3)="","(none)",filtercriteria(A3))

HTH,
Elkar

> We use large Excel files with filters.  I waste time scrolling across columns
> to make sure what filters are still on or off.  Is there a feature in Excel
[quoted text clipped - 9 lines]
>
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=524c4752-8b93-4
5bf-9828-40606d7542b3&dg=microsoft.public.excel.worksheet.functions
Nick Hodge - 21 Jun 2006 20:02 GMT
No, apart from the blue arrows in the filter dropdowns.

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

> We use large Excel files with filters.  I waste time scrolling across
> columns
[quoted text clipped - 12 lines]
>
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=524c4752-8b93-4
5bf-9828-40606d7542b3&dg=microsoft.public.excel.worksheet.functions
 
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.