Show autofilter criteria

Re: Show autofilter criteria

What code are you talking about? This thread makes no sense, should it be part of another question?

Re: Show autofilter criteria

Forum rules

I have added code tags for you in this post.Please read the rules you agreed to particularly concerning Code tags & their use. Thanks

Re: Show autofilter criteria

Do you mean something like this example

Re: Show autofilter criteria

Just a passing comment...
The AutoFilter_Criteria function posted earlier in this thread, and featured in this months OzGrid Excel June 2006 Newsletter, is remarkably like one I wrote a couple opf years ago.

The volatile statement can result in the functions executing needlessly many thousands of times, making non-trivial autofiltered lists run very slowly, especially if code to do other things is piggy backed onto this function.

I got around this by ensuring the function was called from the worksheet like this...

Quote

=AutoFilter_Criteria (C3:C4)

where C3 is the header row. The inclusion of C4 acts as a "changing precedent cell", therefore forcing a recalc of the cell containing the formula only when one of the filters in the autofilter changes, making the worksheet run much faster.

I've not tested it, but I think the code posted above will run fine with the volatile statement removed and no other changes, provided a 2 cell range is passed to it as I described.

Does anyone know how to implement a similar function to highlight when a pivot filter is filtering data??

Re: Show autofilter criteria

the method to avoid "Application.Volatile" seems like an attactive alternative if it can reduce the recalc/UDF trigger to only a change in auto filter.

I tried the method described by BruceMutton:

I did this-
1. commented out the "Application.Volatile" line
2. changed the formula input in the cell with the function from " =AutoFilter_Criteria(D2:D2) "
to " =AutoFilter_Criteria(D2:D3) "

but that does not work. the cell now shows #VALUE!
is there something more to be done?

Using the original form with "Application.Volatile".....
a problem I noticed was that Autofilter "Show All" does not cause the UDF to recalc/update,
only if create my own UDF for ShowAll which has a "Calculate" as the last command.

Any suggestions on addressing these?

Thanks.
Dave

Re: Show autofilter criteria

See: Display AutoFilter Criteria

Re: Show autofilter criteria

XLDude
Not sure if Daves post answers your questions.
I forgot to mention that you also need to declare the range variable with the UDF declaration.

Here is my old code that is a bit more long winded than the Ozgrid code, and somewhat tailored to a particular situation but has the same general structure. I like the single variable passing approach, rather than the two I use. Mine converts date serial numbers to a format you and I can understand.

Call the code below with
=StampCriteria(A11:A12,1)

in the cell formula.
I place this criteria formula in all the cells in the row above the headers, and the range called includes the header and the top cell in the filtered data. The second parameter is the number of the column in the filtered data. (I know, the Ozgrid example makes this redundant)

Then I Conditional Format the criteria cells to be BOLD and BLUE if they do not = "-ALL-" and the filtered columns now stand out in the same way as the filtered rows!

With a bit of wrap text and row auto fit the criteria row height is always as small as possible, and it all fits nicely in a table format without all the white space.

This has worked fine for some years on Excel 97, 2000, 2002 and 2003 so should be fairly robust.

Option Explicit


'***********************************************************************
Function StampCriteria(ByRef rngCell As Range, ByVal iC As Integer)
'rngCell = any cells in autofiltered list, allows Excel to detect change in precedent cells
'iC      = filter number
'Assumes that when iC=1, there may be a date to reformat...
'Debug.Print "StampCriteria UDF called"
Dim f As Filter
Dim c1 As Variant
Dim c2 As Variant


Const cNotSet As String = "-All-"


If rngCell.Parent.AutoFilterMode Then
    Set f = rngCell.Parent.AutoFilter.Filters(iC)
    If f.On Then
        c1 = f.Criteria1      ' "'" &
        If iC = 1 Then
            'need to format dates
            c1 = XtractValue(c1)
        End If
        If f.Operator Then
            Select Case f.Operator
            Case 1
                c1 = c1 & " & " & String(1, 10)
            Case 2
                c1 = c1 & " OR " & String(1, 10)
            Case Else
                c1 = c1 & " " & CStr(f.Operator) & String(1, 10)
            End Select
            c2 = f.Criteria2    'RBM 160704 moved outside If iC...
            If iC = 1 Then
                'need to format dates
                c2 = XtractValue(c2)
            End If
            On Error Resume Next    'Top 10 autofilter causes error
            c1 = c1 & c2 'f.Criteria2
            On Error GoTo 0
        End If  'f.Operator
    Else    'NOT f.On
        c1 = cNotSet
    End If
Else    'NOT AutoFilterMode
    c1 = cNotSet
End If 'AutoFilterMode
StampCriteria = c1
'Range("Criteria").EntireRow.AutoFit    'TOO SLOW to run every recalc
End Function


Function XtractValue(ByVal strText As String) As String
'Find first recognisable number in a text string, convert to date and keep preceeding characters
'Only change strText if strValue is
Dim strValue As String
Dim bFound As Boolean
Dim i As Integer
Dim strchar As String * 1
Dim intPrefixLength As Integer
    strValue = ""
    strchar = ""
    bFound = False
    For i = 1 To Len(strText)
        strchar = Mid(strText, i, 1)
        If (Asc(strchar) > 47 And Asc(strchar) < 58) _
            Or strchar = "." Then 'its a digit 48 to 57
            strValue = strValue & strchar
            bFound = True
        Else
            If bFound = True Then  'have gone beyond first number
                Exit For
            Else
                intPrefixLength = i
            End If
        End If
    Next i
    If Len(strValue) = 5 Then   'assumes a valid date serial number is 5 digits
        XtractValue = Left(strText, intPrefixLength) & Format((CSng(strValue)), "d mmm yy")
    Else
        XtractValue = strText
    End If
End Function

Good luck

Re: Show autofilter criteria

Always good to have more options. Thanks for sharing.