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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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...
:huh:
are you sure this was meant to be in this topic ?
if there is a relation, I do miss something :-?
 
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

S
  • Question Question
VBA - Filter a class array based on class function
Replies
1
Views
226
RoryA
RoryA
R
  • Question Question
Add to Data Validation Values From Data Entry Cell
Replies
4
Views
307
kvsrinivasamurthy
K
V
  • Question Question
autofilter
Replies
2
Views
435
vmjan02
V
FatalLordes
  • Question Question
Expand Cell depending on content
Replies
2
Views
469
FatalLordes
FatalLordes
J
  • Solved
VBA to Search and Extract 7 Values from a Single Cell
Replies
1
Views
192
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,225,482
Messages
6,185,262
Members
453,283
Latest member
Shortm88

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