Display filter criteria

[hujenx]

I have this function to display filtered column criteria in a cell, which works for 2 criteria, but I need to extend to 10 criteria and my VBA's inadequate - can anyone point me in the right direction please?


Function AutoFilter_Criteria(Header As Range) As String

Dim strCri1 As String, strCri2 As String, strCri3 As String




Application.Volatile



With Header.Parent.AutoFilter

With .Filters(Header.Column - .Range.Column + 1)



If Not .On Then Exit Function



strCri1 = .Criteria1



If .Operator = xlOr Then

strCri2 = " , " & .Criteria2



End If



End With

End With



AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2

End Function

 

[VoG]

Try this http://www.mrexcel.com/forum/showthread.php?t=347226

 

[hujenx]

Thanks for the thought, but the code there only displays one criteria, so no advance - in fact one step back!

 

[Tom Urtis]

Just guessing because I'm not clear exactly what you are dealing with, but here is an example I posted some time ago that returns criteria for several filtered columns. You can see how to amend the code to add more UDFs depending on how many columns, and which columns, you are filtering.

Sorry if this is not related to your situation, again, taking a stab at what you might need:

http://www.mrexcel.com/forum/showthread.php?t=229636

 

[hujenx]

Just guessing because I'm not clear exactly what you are dealing with, but here is an example I posted some time ago that returns criteria for several filtered columns. You can see how to amend the code to add more UDFs depending on how many columns, and which columns, you are filtering.

Sorry if this is not related to your situation, again, taking a stab at what you might need:

http://www.mrexcel.com/forum/showthread.php?t=229636

Thanks.

Your code gives the same result as the one I originally posted - max 2 criteria per column.

I have one column and wish to select up to 10 criteria.

It would look like the code is extensible to that, but I just don't know enough to do that.

 

[gruntingmonkey]

Did you ever get to the bottom of this? I'm looking to do the same.

 

[hujenx]

Did you ever get to the bottom of this? I'm looking to do the same.

Hi, it's so long ago that I can't remember! I looked back at my main task at that time, but still wasn't enough to really jog the memory. I have a suspicion though that I may have resolved it just with data validation. If you give me a bit of detail n what you're trying to do I'll look at my file again and see if there's anything there that might help - I did manage to make the whole thing function well eventually and it had many filtering aspects.

 

[gruntingmonkey]

I managed to kind of do what I wanted. Definitely not perfect. Came at it from another angle.