Autofilter with three criteria

[YasserKhalil]

Hello everyone
I have this line of code

Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd, Criteria2:="<>" & "Yasser" & "*"

I need to Criteria3 which is

Criteria3:="<>" & "Any String I will Specify"

How could add this third criteria?

I tried something like that

Sub Testo()Dim ar As Variant
    Dim i As Integer
   
    ar = Array("=FG1F*", "=FG2F*", "=FG1E0??")
   
    For i = 0 To UBound(ar)
        Range("A1:B359").AutoFilter 1, ar(i)
    Next
   
End Sub

But it doesn't work for me

To get it work I had to make a new line after the first line but also doesn't work as expected

 

[Hui]

Try:

Range("A1:A" & LR).AutoFilter Field:=1, _
Criteria1:="<>", Operator:=xlAnd, _
Criteria2:="<>" & "Yasser" & "*", Operator:=xlAnd, _
Criteria3:="<>" & "Any String I will Specify"

 

[YasserKhalil]

Thanks Mr. hui for quick reply
But in fact your reply was the reason for this topic as this was my first try that fails and gives me error
Thanks a lot for sharing

 

[Hui]

I think you'll need to use an Advanced Filter

 

[YasserKhalil]

Is not there any way to user auto filter with multiple criteria? as I need this method .. as I need to filter the rows and then delete hidden rows

 

[Hui]

Advanced Filter can do that a lot easier as it can filter in place or to another location

With the filter you are using , You can also use wildcards * and ? in your filter strings eg "*Smith*"

Can you supply a sample file with the real criteria you want to apply

 

[YasserKhalil]

Here's a sample workbook

 

[Marc L]

Hi !​

as I need to filter the rows and then delete hidden rows

Bad way ! Easier to hide the rows to keep and delete the visible ones …

If you can manually use an autofilter for your need, just use Macro Recorder …

If not, an advanced filter is the right and easy way,
10/11 years old kids have succeeded to use it !
See Excel help, the tutorial from this forum as well the samples in threads.
And also you can activate Macro Recorder before to operate it …
​

 

[Hui]

If you simply apply a filter by Yasser Khalil* and "Thank you*"
You will get the result as it will exclude the Blank rows as they don't match the criteria

 

[Debaser]

One thing you can do with autofilter is to loop through the data and create a list of valid values, rather than trying to exclude three criteria:

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

 

[Hui]

ActiveSheet.Range("$A$1:$A$18").AutoFilter Field:=1, Criteria1:= _
"=Yasser Khalil**", Operator:=xlOr, Criteria2:="=Thank**"

 

[YasserKhalil]

Thanks Mr. Hui for reply but it is not exactly what I need .. I need to hide empty rows / rows starts with "Yasser Khalil " & "8" / rows that contain "Thank you very much" .. In fact it is complicated issue for me not as shown for you mr. MarcL .. It would be part of a nother big code so I was searching for using multiple criteria (I can do it in other ways) ...
In fact Mr. Debaser's solution is the best solution for my case as it gives me ore flexibilty

Thanks a lot for everyone who share this issue
Thank you very much

 

[Hui]

I'm sure if you get your criteria right you can do it with 2 criteria

Otherwise use an advanced filter

 

[YasserKhalil]

Thanks Mr. Hui for help
You are right I can do it with 2 criteria and in another line to finish the task with another criteria
Best Regards

 

[Marc L]

Since Excel 2007 AutoFilter can have more than 2 criterion on a column
just by using an array within Criteria1 …
As you can see in threads of this forum via a tiny search !
Or maybe just using Macro Recorder …
​

 

[YasserKhalil]

Thanks Mr. MarcL
When I used Macro recorder as you told me .. when trying to execute it again it gives me error.. and that is very weird ..!!
And I don't trust in recording although it is very useful in many cases
Generally it is solved in perfect way using the Debaser's method