# Getting autofilter info

#### rline101

##### Board Regular
I have some data autofiltered. So eg I have 3 coluumns of grade, gender and house (for a sports carnival). So I select, say, grade to be 9, gender to be boys and house to be Yellow. Is it possible to have those three things "9", "Girls" and "Yellow" automatically returned in another cell nearby?

Thanks.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Tom Urtis

##### MrExcel MVP
Expanding on your example, assume you have a 3-column table with row 1 as the header row. In column A is your field for Grade, in column B is your field for Gender, and column C is your field for House.

Step 1
Stick this code into a standard VBA module:

Function FilterCriteria(Rng As Range) As String
Application.Volatile
'By Stephen Bullen (slightly modified by me for this post)
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:
If ActiveSheet.AutoFilterMode = True Then
If Filter <> "" Then
FilterCriteria = Right(Filter, Len(Filter) - WorksheetFunction.Search("=", Filter))
End If
End If
End Function

Step 2

In cell F1 enter this formula to display the filter criteria for column A (Grade):
=FilterCriteria(A1)

In cell G1 enter this formula to display the filter criteria for column B (Gender):
=FilterCriteria(B1)

In cell H1 enter this formula to display the filter criteria for column C (House):
=FilterCriteria(C1)

Now when you AutoFilter that table, you will see the criteria in F1:H1 for respective field headers A1:C1.

#### rline101

##### Board Regular
Tom you are a legend! Thanks heaps. This worked 1st time.

Cheers!

Replies
2
Views
217
Replies
9
Views
125
Replies
3
Views
294
Replies
1
Views
147
Replies
7
Views
360

1,136,366
Messages
5,675,354
Members
419,564
Latest member
Phil57

### 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.

### Which adblocker are you using?

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

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