• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Autofilter multiple criteria

YasserKhalil

Well-Known Member
Hello experts
I have a sample file with a code for Mr. Debaser ..It filters and exclude certain filter values ..
I need simply to verse it .. I mean to include the filter values not to exclude them
The expected results to filter the yellow cells
Code:
Sub RunAutoFilter()
    Dim vCriteria
    Dim rgData As Range
   
    Set rgData = Range("A1", Cells(Rows.Count, "A").End(xlUp))
    vCriteria = GetOtherValues(rgData.Offset(1).Resize(rgData.Rows.Count - 1), vbNullString, "Yasser Khalil*", "Thank you very much")
    If Not IsEmpty(vCriteria) Then rgData.AutoFilter Field:=1, Criteria1:=vCriteria, Operator:=xlFilterValues
End Sub

Function GetOtherValues(rgData As Excel.Range, ParamArray excludeVals()) As Variant
    Dim N      As Long
    Dim x      As Long
    Dim bSkip  As Boolean
    Dim vData

    vData = rgData.Value2
    With CreateObject("Scripting.Dictionary")
        For N = LBound(vData, 1) To UBound(vData, 1)
            bSkip = False
            For x = LBound(excludeVals) To UBound(excludeVals)
                If LCase$(vData(N, 1)) Like LCase$(excludeVals(x)) Then
                    bSkip = True
                    Exit For
                End If
            Next x
            If Not bSkip Then .Item(CStr(vData(N, 1))) = Empty
        Next N
       
        If .Count > 0 Then
            GetOtherValues = .Keys
        End If
    End With
End Function

Thanks advanced for help
 

Attachments

  • AutoFilter Muliple Criteria Debaser.xlsm
    17 KB · Views: 3
Thanks a lot
I could figure it out by editing this line
Code:
If bSkip Then .Item(CStr(vData(N, 1))) = Empty
I omitted NOT from this line
 
Back
Top