none
User Defined Function to Display AutoFilter Criteria for More Than Two Criteria in Excel 2007 / Excel 2010 RRS feed

  • Question

  • I have been using the Stephen Bullen function to display Excel 2003's Autofilter Criteria (only 2 criteria) for years and now I really want to have an equivalent for the multiple-value autofilters in Excel 2007+ - (I am using Excel 2010).

    I've tried the following but it does not work (and yes I know this would only work on more than 2 criteria):

     

    Function FilterCrit(rng As Range) As String

        Dim sFilter As String

        sFilter = ""

        On Error GoTo Finish

        With rng.Parent.AutoFilter

            If Intersect(rng, .Range) Is Nothing Then GoTo Finish

            With .Filters.Item(rng.Column - .Range.Column + 1)

                If Not .On Then GoTo Finish

    For i = 1 To .Count

    sFilter = sFilter & " " & .Criteria1(i) ' this is where there is an error and it jumps to Finish:

    Next

            End With

        End With

    Finish:

        FbLFilterCrit = sFilter

    End Function

     

    Any ideas as to why it fails?

     

    Thanks in advance,


    Lou Sigety
    Friday, October 29, 2010 10:48 AM

Answers

  • Ok LOu, how about this
     
    Public Function ShowFilter(rng As Range)
    Dim filt As Filter
    Dim sCrit1 As String
    Dim sCrit2 As String
    Dim sOp As String
    Dim lngOp As Long
    Dim lngOff As Long
    Dim frng As Range
    Dim sh As Worksheet
    Dim i As Long
     

        Set sh = rng.Parent
        If sh.FilterMode = False Then
            ShowFilter = "No Active Filter"
            Exit Function
        End If
        Set frng = sh.AutoFilter.Range
     
        If Intersect(rng.EntireColumn, frng) Is Nothing Then
            ShowFilter = CVErr(xlErrRef)
        Else
            lngOff = rng.Column - frng.Columns(1).Column + 1
            If Not sh.AutoFilter.Filters(lngOff).On Then
                ShowFilter = "No Conditions"
            Else
                Set filt = sh.AutoFilter.Filters(lngOff)
                On Error Resume Next
                lngOp = filt.Operator
                If lngOp = xlFilterValues Then
               
                    For i = LBound(filt.Criteria1) To UBound(filt.Criteria1)
                        sCrit1 = sCrit1 & filt.Criteria1(i) & " or "
                    Next i
                    sCrit1 = Left(sCrit1, Len(sCrit1) - 3)
                Else
               
                    sCrit1 = filt.Criteria1
                    sCrit2 = filt.Criteria2
                    If lngOp = xlAnd Then
                        sOp = " And "
                    ElseIf lngOp = xlOr Then
                        sOp = " or "
                    Else
                        sOp = ""
                    End If
                End If
                ShowFilter = sCrit1 & sOp & sCrit2
            End If
        End If
    End Function

    --

    HTH
     
    Bob

    Thanks for the reply Bob, but I already had that level of capability - that only COMPLETELY works for Excel 2003 and prior for when there were only two criteria allowed.  Starting in Excel 2007, the ability to pick more than two items from an Autofilter drop down list makes that function show nothing if more than two items are selected.

    If more than two items are selected, .Criteria1 contains an array of all selected values and I need some way of assigning the contents of that array to a string which would then be the output of the User Defined Function.

    The UDF I provided in this thread fails to assign the contents of the .Criteria1 array to a string... that is what I need help with... then I can write a more robust UDF that will check how many Criteria are selected and invoke an Array selection or the old Excel 2003 way of displaying what is filtered.

    Any help would be appreciated.

    Thanks,

     


    Lou Sigety
    Friday, October 29, 2010 5:08 PM

All replies

  • I have always used this, and it works in 2007
     
    Public Function ShowFilter(rng As Range)
    Dim filt As Filter
    Dim sCrit1 As String
    Dim sCrit2 As String
    Dim sop As String
    Dim lngOp As Long
    Dim lngOff As Long
    Dim frng As Range
    Dim sh As Worksheet
     
        Set sh = rng.Parent
        If sh.FilterMode = False Then
            ShowFilter = "No Active Filter"
            Exit Function
        End If
        Set frng = sh.AutoFilter.Range
     
        If Intersect(rng.EntireColumn, frng) Is Nothing Then
            ShowFilter = CVErr(xlErrRef)
        Else
            lngOff = rng.Column - frng.Columns(1).Column + 1
            If Not sh.AutoFilter.Filters(lngOff).On Then
                ShowFilter = "No Conditions"
            Else
                Set filt = sh.AutoFilter.Filters(lngOff)
                On Error Resume Next
                sCrit1 = filt.Criteria1
                sCrit2 = filt.Criteria2
                lngOp = filt.Operator
                If lngOp = xlAnd Then
                    sop = " And "
                ElseIf lngOp = xlOr Then
                    sop = " or "
                Else
                    sop = ""
                End If
                ShowFilter = sCrit1 & sop & sCrit2
            End If
        End If
    End Function
    You use it like so
     
    =showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+32)
     
    The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed

    --

    HTH
     
    Bob

    I have been using the Stephen Bullen function to display Excel 2003's Autofilter Criteria (only 2 criteria) for years and now I really want to have an equivalent for the multiple-value autofilters in Excel 2007+ - (I am using Excel 2010).

    I've tried the following but it does not work (and yes I know this would only work on more than 2 criteria):

     

    Function FilterCrit(rng As Range) As String

        Dim sFilter As String

        sFilter = ""

        On Error GoTo Finish

        With rng.Parent.AutoFilter

            If Intersect(rng, .Range) Is Nothing Then GoTo Finish

            With .Filters.Item(rng.Column - .Range.Column + 1)

                If Not .On Then GoTo Finish

    For i = 1 To .Count

    sFilter = sFilter & " " & .Criteria1(i) ' this is where there is an error and it jumps to Finish:

    Next

            End With

        End With

    Finish:

        FbLFilterCrit = sFilter

    End Function

     

    Any ideas as to why it fails?

     

    Thanks in advance,


    Lou Sigety
    Friday, October 29, 2010 4:27 PM
  • Thanks for the reply Bob, but I already had that level of capability - that only COMPLETELY works for Excel 2003 and prior for when there were only two criteria allowed.  Starting in Excel 2007, the ability to pick more than two items from an Autofilter drop down list makes that function show nothing if more than two items are selected.

    If more than two items are selected, .Criteria1 contains an array of all selected values and I need some way of assigning the contents of that array to a string which would then be the output of the User Defined Function.

    The UDF I provided in this thread fails to assign the contents of the .Criteria1 array to a string... that is what I need help with... then I can write a more robust UDF that will check how many Criteria are selected and invoke an Array selection or the old Excel 2003 way of displaying what is filtered.

    Any help would be appreciated.

    Thanks,

     


    Lou Sigety
    • Edited by Lou Sigety Friday, October 29, 2010 4:52 PM clarification
    Friday, October 29, 2010 4:48 PM
  • Ok LOu, how about this
     
    Public Function ShowFilter(rng As Range)
    Dim filt As Filter
    Dim sCrit1 As String
    Dim sCrit2 As String
    Dim sOp As String
    Dim lngOp As Long
    Dim lngOff As Long
    Dim frng As Range
    Dim sh As Worksheet
    Dim i As Long
     

        Set sh = rng.Parent
        If sh.FilterMode = False Then
            ShowFilter = "No Active Filter"
            Exit Function
        End If
        Set frng = sh.AutoFilter.Range
     
        If Intersect(rng.EntireColumn, frng) Is Nothing Then
            ShowFilter = CVErr(xlErrRef)
        Else
            lngOff = rng.Column - frng.Columns(1).Column + 1
            If Not sh.AutoFilter.Filters(lngOff).On Then
                ShowFilter = "No Conditions"
            Else
                Set filt = sh.AutoFilter.Filters(lngOff)
                On Error Resume Next
                lngOp = filt.Operator
                If lngOp = xlFilterValues Then
               
                    For i = LBound(filt.Criteria1) To UBound(filt.Criteria1)
                        sCrit1 = sCrit1 & filt.Criteria1(i) & " or "
                    Next i
                    sCrit1 = Left(sCrit1, Len(sCrit1) - 3)
                Else
               
                    sCrit1 = filt.Criteria1
                    sCrit2 = filt.Criteria2
                    If lngOp = xlAnd Then
                        sOp = " And "
                    ElseIf lngOp = xlOr Then
                        sOp = " or "
                    Else
                        sOp = ""
                    End If
                End If
                ShowFilter = sCrit1 & sOp & sCrit2
            End If
        End If
    End Function

    --

    HTH
     
    Bob

    Thanks for the reply Bob, but I already had that level of capability - that only COMPLETELY works for Excel 2003 and prior for when there were only two criteria allowed.  Starting in Excel 2007, the ability to pick more than two items from an Autofilter drop down list makes that function show nothing if more than two items are selected.

    If more than two items are selected, .Criteria1 contains an array of all selected values and I need some way of assigning the contents of that array to a string which would then be the output of the User Defined Function.

    The UDF I provided in this thread fails to assign the contents of the .Criteria1 array to a string... that is what I need help with... then I can write a more robust UDF that will check how many Criteria are selected and invoke an Array selection or the old Excel 2003 way of displaying what is filtered.

    Any help would be appreciated.

    Thanks,

     


    Lou Sigety
    Friday, October 29, 2010 5:08 PM
  • Thanks Bob!  That works quite well.

    I initially had a problem with the function not updating but that was due to me not including the entire range.

    Thanks so much for your help.

     


    Lou Sigety
    • Edited by Lou Sigety Friday, October 29, 2010 6:45 PM correction
    Friday, October 29, 2010 6:42 PM