Getting autofilter info

[rline101]

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.

 

[Tom Urtis]

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]

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


Cheers!