Excel UDF to values based on Filtered or Unfiltered Values in an Autofilter.

[Biz]

Dear All,

I found an Excel UDF that shows items filtered or checked in Autofilter. I wanted both show items unfiltered or unchecked in Autofilter based boolean statement passed optional statement.

Does anyone know how I can pass a Boolean statement to get Filtered or Unfiltered Items in Autofilter?

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

Your help would be greatly appreciated.


Kind Regards


Biz