Applying multiple critera to an autofilter

[kc_native]

I was fortunate to get the following code from a board regular, and it solved my dilema at the time, but now I would like to add multiple criteria to the code, and I can't seem to get it to work. I'm sure it is in the structure I am using, so can anyone tell me how to structure this code if I want to add additional criteria, with Or as the operator? I want to filter out all but a list of names I will be entering as the criteria, so I will be using "<> (names)" in my code. Thanks!

[COLOR=black][FONT=Courier New]Sub delrow()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   Dim num As Double<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   num = InputBox("What is the Criteria Number", "Number")<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   With Range("E2", Range("E" & Rows.Count).End(xlUp))<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]       .AutoFilter Field:=1, Criteria1:="<" & num, Operator:=xlAnd<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]       .SpecialCells(xlCellTypeVisible).EntireRow.Delete (xlUp)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   End With<o:p></o:p>[/FONT][/COLOR]

 

[pbornemeier]

Reconsidering my answer

 

[kc_native]

Phil, to complicate matters, the names I will be looking for are in the following format: Last, First. Can I use "<> Last, First" in my criteria, or do I have to something different with the name structure?

Thanks.

 

[kc_native]

I think I may have made this muddier than I needed to by the code example I attached, so here is what I am trying to do, and I am getting a Compile Error: Named argument not found.

With Range("H:H")
    .AutoFilter Criteria1:="<> Last1, First1", Operator:=xlOr, Criteria2:="<> Last2, First2", Operator:=xlOr, _
                Criteria3:="<> Last3, First3", Operator:=xlOr, Criteria4:="<> Last4, First4", Operator:=xlOr, _
                Criteria5:="<> Last5, First5", Operator:=xlOr
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete (xlUp)
End With

 

[pbornemeier]

There can only be 2 Criteria when using the method in post #4
What columns contain the Last name? First name?
The technique that I am working on will be case sensitive - is that desired?

With Excel 2007 and later, you can use this technique to match multiple names in autofilter:
ActiveSheet.Range("$A$1:$R$10259").AutoFilter Field:=4, Criteria1:=Array( _
"CC00", "CP00", "DT00", "RX00"), Operator:=xlFilterValues
To match all but certain names, you have to generate a list of all names, remove the ones you don't want then match all that are left.

In the past, I would copy the original list to a working worksheet then filter it to show one of the items that I did not want the select the visible rows and delete entire row, clear autofilter then perform a new autofilter on what remained with the next item I did not want. After a time, all that was left were the items I wanted, and the original data was intact.

 

[pbornemeier]

It would be easiest to have a single column that contained the Last, First structure that you were filtering by. It would be best if all of the data in that column were forced to be upper case.

 

[pbornemeier]

This is the bit of code that I mentioned earlier that displays and removes unwanted filtered rows from a list:

    ActiveSheet.AutoFilterMode = False
    lngLastInputRow = Cells(Rows.Count, "A").End(xlUp).Row
    Selection.AutoFilter Field:=3, Criteria1:="BLUE"
    lngX = WorksheetFunction.Subtotal(3, Columns(1)) 'Visible Row Count
    If lngX > 1 Then
        Range("A2:A" & lngLastInputRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Debug.Print "Removed " & lngX - 1 & " items " & "column 3 criterion of BLUE"
        Application.StatusBar = "Removed " & lngX - 1 & " items " & "column 3 criterion of BLUE"
    End If
    ActiveSheet.AutoFilterMode = False

Repeat as many times as required to remove unwanted rows.

 

[kc_native]

There can only be 2 Criteria when using the method in post #4
What columns contain the Last name? First name?
The technique that I am working on will be case sensitive - is that desired?

With Excel 2007 and later, you can use this technique to match multiple names in autofilter:
ActiveSheet.Range("$A$1:$R$10259").AutoFilter Field:=4, Criteria1:=Array( _
"CC00", "CP00", "DT00", "RX00"), Operator:=xlFilterValues
To match all but certain names, you have to generate a list of all names, remove the ones you don't want then match all that are left.

In the past, I would copy the original list to a working worksheet then filter it to show one of the items that I did not want the select the visible rows and delete entire row, clear autofilter then perform a new autofilter on what remained with the next item I did not want. After a time, all that was left were the items I wanted, and the original data was intact.

Okay, so I could use the Array to contain the list of names I want to keep, and use Criteria1:<> Array("Name1", "Name2", "Name3", ....)?

All the names are in column H, in the Last, First format, and are case sensitive.

I put together the following code in lieu of using autofilter, and it works fine, but it takes a while to execute since it steps through each row. I am using Name1, Name2, etc. to denote the actual last names of the individuals I have in my code.

For lngRow = Cells(Rows.Count, 1).End(xlUp).Row - 1 To 6 Step -1
    If (Left(Cells(lngRow, 8), 6) <> "Last1" And Left(Cells(lngRow, 8), 7) <> "Last2" And _
        Left(Cells(lngRow, 8), 7) <> "Last3" And Left(Cells(lngRow, 8), 5) <> "Last4" And _
        Left(Cells(lngRow, 8), 8) <> "Last5" And Left(Cells(lngRow, 8), 6) <> "Last6" And _
        Left(Cells(lngRow, 8), 5) <> "Last7" And Left(Cells(lngRow, 8), 7) <> "Last8" And _
        Left(Cells(lngRow, 8), 9) <> "Last9" And Left(Cells(lngRow, 8), 7) <> "Last10") Then
            Rows(lngRow).Delete (xlUp)
    End If
Next lngRow

 

[pbornemeier]

I don't think you can use <> with the array and the xlFilterValues operator.

This code uses autofilter to display each desired name's rows and copy them to another worksheet. It may work a bit faster than your last posted code:

Sub ExtractCertainRows()
    Dim aryNamesToKeep As Variant
    Dim lLastDataRow As Long
    Dim lX As Long
    Dim lVisibleRowCount As Long
    Dim lNextWriteRow As Long
 
    aryNamesToKeep = Array("Last1", "Last2", "Last3", "Last4", "Last5", "Last6", "Last7", "Last8", "Last9", "Last10")
 
    'Clear/Create output worksheet
    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets("GoodStuff").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Worksheets.Add(after:=Sheets(Sheets.Count)).Name = "GoodStuff"
 
    Worksheets("Input").Select 'Source of all data
    ActiveSheet.AutoFilterMode = False
 
    lLastDataRow = Cells(Rows.Count, "A").End(xlUp).Row
 
    For lX = LBound(aryNamesToKeep) To UBound(aryNamesToKeep)
        Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=aryNamesToKeep(lX)
        lVisibleRowCount = WorksheetFunction.Subtotal(3, Columns(1)) '3 = Visible Row Count
        If lVisibleRowCount > 1 Then '> 1 to ignore header row
            'Copy good data to other worksheet
            With Worksheets("GoodStuff")
                lNextWriteRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            End With
            Range("A2:A" & lLastDataRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
                Destination:=Worksheets("GoodStuff").Cells(lNextWriteRow, 1)
        End If
    Next
 
    ActiveSheet.AutoFilterMode = False
 
End Sub