How to pull an autofilter value?

S

Sanador

Board Regular
Joined
Sep 24, 2004
Messages
108
Is there an easy way to automatically list the value upon which a list is filtered? For example, if my data located in A3:E1000 were filtered on column D for "Matthew," is there a formula that I could place in D2 that would automatically calculate "Matthew" in that cell?

Is that enough info? Thanks!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Hi,

to my sense you will need code
try this
(probably already exists somewhere, but I like to invent the wheel sometimes)
Code: 
Option Explicit

Sub retrieve_autofilter_criteria()
'Erik Van Geit
'070425

Dim i As Long
Dim msg As String
Dim check As String
Dim yes As Boolean

    With ActiveSheet
        If .AutoFilterMode Then
        msg = "field" & vbTab & "crit1" & vbTab & "crit2" & vbTab & "operator" & vbLf

            For i = 1 To .AutoFilter.Filters.Count
                With .AutoFilter.Filters(i)
                
                    If .On Then
                    yes = True
                    msg = msg & i & vbTab & .Criteria1
                    check = vbNullString
                    On Error Resume Next
                    check = .Criteria2
                    msg = msg & vbTab & IIf(LenB(check) > 0, check, "     ")
                    On Error GoTo 0
                    msg = msg & vbTab & filteroperator(.Operator) & vbLf
                    Else
                    msg = msg & i & vbTab & "unfiltered"
                    End If

                End With
            Next i
        msg = IIf(yes, msg, "no filters on")
        MsgBox msg, 64, "Autofilter current criteria"
        
        Else
        MsgBox "no autofilter on this sheet", 64, "Autofilter"
        End If
    End With

End Sub

Function filteroperator(i As Long)
Dim operators As Variant
operators = Array("", "xlAnd", "xlOr", "xlTop10Items", "xlBottom10Items", "xlTop10Percent", "xlBottom10Percent")
filteroperator = operators(i)

End Function
if this works for you, we can adapt it to write on the sheet (I wonder how we can retrieve the current "fields" to put the info in the right locations, but it's late here)

kind regards,
Erik
 
Upvote 0
Is there an easy way to automatically list the value upon which a list is filtered? For example, if my data located in A3:E1000 were filtered on column D for "Matthew," is there a formula that I could place in D2 that would automatically calculate "Matthew" in that cell?

Is that enough info? Thanks!
Click to expand...

=INDEX(D3:D1000,MATCH(1,SUBTOTAL(3,OFFSET(D3:D1000,ROW(D3:D1000)-ROW(D3),,1)),0))

which needs to be confirmed with control+shift+enter, not just with enter.
 
Upvote 0
Aladin,

Your formula will display the contents of the first visible cell.
I was trying to display all possibilities, but yours matches the example
filtered on column D for "Matthew," is there a formula that I could place in D2 that would automatically calculate "Matthew" in that cell?
Click to expand...
seen from that point of view mine is overkill.

so, Sanador, I strongly suggest you try out the formula first :)

greetings,
Erik
 
Upvote 0
That works... Thank you everyone! I am curious, however, about Erik's thought of including all possibilities. If the data in column D included Jane, Jon, Joe, and Jeff besides Matthew (for example), and the column were left unfiltered, what would your solution produce?

Thanks again!
 
Upvote 0
and the column were left unfiltered, what would your solution produce?
Click to expand...
did you try the code?

If the column were left unfiltered, it would not be in the list of filtered columns, so you would know there is no active filter for that column. Of course the code can be edited to have an explicit message for each non filtered column.

after the first "else" (put there for the purpose)
you can insert a line
Code: 
msg = msg & i & vbTab & "unfiltered"
I'll add it to the code now.

best regards,
Erik
 
Upvote 0
Hi This sounds like it would work for my problem
Hi All,

This is my reference spreadsheet setup

A1 = Date
B1 = Name
C1 = File #
D1 = Subject
E1 = Details

I wanted a summary spreadsheet to lookup D1 if it matches the subject topic (example Apples) then it would place the corresponding information for that row A1 to E1. If False it would just reserve that line for the next true result?

But how and where would I enter the code?
 
Upvote 0
Hi This sounds like it would work for my problem
Hi All,

This is my reference spreadsheet setup

....

But how and where would I enter the code?
Click to expand...
:unsure:
are you sure this was meant to be in this topic ?
if there is a relation, I do miss something :confused:
 
Upvote 0
Me again... I am currently working on another project in which I'd like I nice, bold display of the year being viewed by the user. Various years are listed in column D and the entire sheet is autofiltered. Ideally, I'd like cell H5 to reflect the status of the filter on column D. For example, if:

Col D filtered on "2007-2008" --- H5 = "2007-2008" minus the quotes.

Col D filtered to include "2007-2008" and "2006-2007" --- H5 = "2006-2008" or some combination of the years.

Col D filtered to include all options --- H5 = "ALL" or something to that effect.

I've tried the code that Erik offered but seemingly nothing happened.

Can this be done?

Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Chris_010101
  • Solved
Excel VBA Help, Filters
Replies
3
Views
410
Fluff
Fluff
D
  • Question
Pull cell data from multiple sheets into a list on a master sheet
Replies
2
Views
608
Sanjeev1976
Sanjeev1976
E
  • Question
Data Filtering – Help needed with some nervous Excel users!
Replies
9
Views
744
Peter_SSs
Peter_SSs
G
  • Question
Month to date, last month to date, last year to date
Replies
2
Views
469
gazmoz17
G
J
  • Solved
Remove cell contents based on date - VBA
Replies
3
Views
473
kevin9999
K

Forum statistics

Threads
1,214,653
Messages
6,120,751
Members
448,989
Latest member
mariah3

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