Identifying Filtered Data

As a starting point, let's assume:

- Your header row is 9
- Your data records start in row 10
- You want row 1 to be the row that displays the filter criteria
- You are filtering manually


In a standard module, enter this UDF by Stephen Bullen:

Function FilterCriteria(Rng As Range) As String
Application.Volatile
'By Stephen Bullen
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:
FilterCriteria = "List is being filtered for " & Filter
End Function


In cell A1 enter this formula"
=FilterCriteria(A9)

Drag or copy and paste the formula from A1 into all cells up to and including cell J1 or the column that contains the last field your filtered data really involves.

Now when you filter manually, the criteria for each field will be displayed in row 1 of that field.

If the above assumptions are incorrect then change the UDF formula's range argument parameter and placement as desired.

Hi Tom - and thank you for pointing me in the right direction on this. I tried it and it works for data in column A. Unfortunately, I should have described my sheet a little better, because I need to expand upon what your solution does.

Here's a more complete explanation:

I am triggering the use of autofilter from a button on the sheet connected to this simple code:

You correctly assumed that my data started in row 10, with headings in row 9. The issue is that I have 13 columns (out to and including M) where the users might filter the list from.

So my issue is how to display in text which of any of these 13 columns the list is being filtered by, and if possible the choice within that column that is being used as the criteria.

Sorry for not being more complete previously.

Any thoughts?

Fujirich said:

So my issue is how to display in text which of any of these 13 columns the list is being filtered by, and if possible the choice within that column that is being used as the criteria.

Click to expand...

Did you read what I posted, which was to copy the UDF
=FilterCriteria(A9)
from A1 across to the last column in row 1 which holds your filter range, in your case that would be from A1:M1.

Now, because any one of the 13 fields can be filtered, with more than one field being filter-able at any time since that's what AutoFilter allows, this Function modification will show only the criteria being filtered in row 1 of whatever column(s) / field(s) are being filtered. And, it will only be triggered when the worksheet is in AutoFilter mode.


Function FilterCriteria(Rng As Range) As String
Application.Volatile
'By Stephen Bullen
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 = "List is being filtered for " & Filter
End If
End If
End Function

Tom - thanks again, and you're right; I didn't pay attention to your comment about pasting the formula across the columns.

This works great. I can use the results I get here in a formula to display the text I want. One question: the result of this currently is is displayed as "List being filtered for =result". So if I'm filtering in a State column for any listings in CA, the test returned is "List being filtered for =CA". I can get rid of the "List being filtered for" text, but the equals sign remains. What change in the code would only display the result (in this case, just "CA")

Thanks so much!

The reason I had that text in there was that you originally wrote this:
"2) In a text line, I'd like to display something like List is being filtered by (column heading) and (item selected)"


Anyway, this should be the coop dee grass:


Function FilterCriteria(Rng As Range) As String
Application.Volatile
'By Stephen Bullen (modified by Tom Urtis 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) - Application.Search("=", Filter))
End If
End If
End Function

Tom - you are correct; I did originally ask for the extra text. However, when I saw how this solution returned the data, I realized that to get to where I wanted to be I would have to add some steps, and in that realization it was better to just have the filtered criteria as the result.

Thanks so much for all your input and help. The value of folks like you who help noobs like me on this great site is really priceless!

Rich

Tom - I don't know if you're still out there watching this thread, but a bit of trouble has appeared.

Using the code you last provided:

Any time I enter data in the range A10:M1009 (the range that will be unprotected for my user to enter their data into), I get a "#VALUE!" error in any of the cells where "=FilterCriteria(A9)" is pasted.

More info - just tried disabling a date stamping macro I had on this page and that stopped the "#Value!" error problem. Here's that code:

Obviously these are not playing well together, but I know so little of VBA I'm not sure how to correct.

Any thoughts?

Thanks!

Rich

Substitute the change event you have in your worksheet module with this one:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Row > 9 And .Row < 1010 Then
Application.EnableEvents = False
Range("M7").Value = Date
Application.EnableEvents = True
End If
End With
End Sub

That did it - Tom; you are definately the man! Thanks!!