Display Filter Selection

A

Andre Basson

New Member
Joined
Oct 15, 2008
Messages
9
Hi, Would like to know whether it is possible to display your selected choice when using Auto Filter - e.g. if you have chosen "All" it must display "All" in the cell you have chosen for this pupose - typically a cell in the header in the column being filtered. Tx Andre
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Hello and welcome to MrExcel.

This came from John Walkenbach if I remember correctly. Insert this UDF in a standard module:

Code: 
Function FilterCrit(Rng As Range) As String
    Dim Filter As String
    Filter = "{All}"
    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:
    FilterCrit = Filter
End Function

Then on your worksheet enter the formula

=filtercrit(A3)

where A3 is within the range being filtered.
 
Upvote 0
VoG II said:
Hello and welcome to MrExcel.

This came from John Walkenbach if I remember correctly. Insert this UDF in a standard module:

Code: 
Function FilterCrit(Rng As Range) As String
    Dim Filter As String
    Filter = "{All}"
    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:
    FilterCrit = Filter
End Function

Then on your worksheet enter the formula

=filtercrit(A3)

where A3 is within the range being filtered
Click to expand...

Hi Peter (I guess, I referred you by your real name ? and I hope it is ok with you ?)

This would prolly display the Filtered criteria in the cell where the formula has been entered, correct ? What if the user wants the selected filtered criteria to be displayed as its heading ? Is it possible ?
 
Upvote 0
Note that there will be some issues here if using Excel 2007 because there are so many AutoFilter choices/combinations possible.
 
Upvote 0
Stormseed said:
Hi Peter (I guess, I referred you by your real name ? and I hope it is ok with you ?)

This would prolly display the Filtered criteria in the cell where the formula has been entered, correct ? What if the user wants the selected filtered criteria to be displayed as its heading ? Is it possible ?
Click to expand...

Hi

I'm not sure quite what you mean ... In the example I gave, the formula could be entered in A1 to display the criteria being used.

Peter - good point re XL 2007.
 
Upvote 0
Hi All,
VoG II's advice worked - thanks very much.
Had a problem logging back in though, hence the delay. Not sure what I did wrong.
Regards A
 
Upvote 0
VoG said:
Hello and welcome to MrExcel.

This came from John Walkenbach if I remember correctly. Insert this UDF in a standard module:

Code: 
Function FilterCrit(Rng As Range) As String
    Dim Filter As String
    Filter = "{All}"
    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:
    FilterCrit = Filter
End Function

Then on your worksheet enter the formula

=filtercrit(A3)

where A3 is within the range being filtered.
Click to expand...

Thanks, this really helped me getting the user custom criterias from our excel files!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Wrapping Indirect around a Filter
Replies
3
Views
69
Cubist
Cubist
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
408
Peter_SSs
Peter_SSs
K
  • Question Question
[VBA] Applying filter to multiple tables on same sheet
Replies
2
Views
306
kleinmat
K
J
  • Question Question
Filter ListBox on a UserForm using a ComboBox
Replies
1
Views
357
DanteAmor
DanteAmor
D
  • Solved
Using VBA to add a name to a cell
Replies
6
Views
225
deftincu
D

Forum statistics

Threads
1,224,548
Messages
6,179,445
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top