Addition to Delete Row Criteria

[wayneshirley]

Hi there,

I have a piece of vba that looks at data in column A. When it finds "7999" and "8999" it deletes the entire row.

Is it possible to add "A401" and "A451" to this?

Code
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="7999", Operator:=xlOr, _
Criteria2:="8999"
.Offset(1).EntireRow.Delete
.AutoFilter
End With

Thank you.

 

[villy]

Hi there,

I have a piece of vba that looks at data in column A. When it finds "7999" and "8999" it deletes the entire row.

Is it possible to add "A401" and "A451" to this?

Code
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="7999", Operator:=xlOr, _
Criteria2:="8999"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Thank you.

Try this...

With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="7999", Operator:=xlOr, _
Criteria2:="8999", Operator:=xlOr, _
Criteria3:="A401", Operator:=xlOr, _
Criteria4:="A451"
.Offset(1).EntireRow.Delete
.AutoFilter
End With

 

[Marcelo Branco]

Hi,

Maybe this can help
http://www.rondebruin.nl/delete.htm#AutoFilter

take a look at More than two criteria

M.

 

[wayneshirley]

I have tried this code but Criteria3:= is highlighted with the error message "Compile Error: Named argument not named."

Can you help please?

 

[Marcelo Branco]

I have tried this code but Criteria3:= is highlighted with the error message "Compile Error: Named argument not named."

Can you help please?

Auto Filter has a Max of 2 criteria. Have you tried the link i suggested?

M.

 

[Trebor76]

Hi there,

Try this alternative method that deletes the rows for each element in the 'vMyArray' array (which can simply added to).

Note I've adapted this from Rick Rothstein's very clever Delete Row With Specific Word

Regards,

Robert

'http://www.mrexcel.com/forum/showthread.php?t=567491
Sub Macro1()

    Dim vMyArray As Variant
    Dim vArrayItem As Variant
        
    vMyArray = Array("7999", "8999", "A401", "A451")
    
    Application.ScreenUpdating = False
    
    For Each vArrayItem In vMyArray
                
        With Range("A1", Range("A" & Rows.Count).End(xlUp))
            .Replace vArrayItem, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
            On Error Resume Next ' Account for no cells being found.
                .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
            On Error GoTo 0
        End With
        
    Next vArrayItem
    
    Application.ScreenUpdating = True

End Sub