Deleting Rows using a Macro

[ultracyclist]

I’m using the following macro to delete all rows that match my Array. Due to the number of different sites (>30), I would like to revise the array to keep all rows that match 2-3 criteria, plus any rows that have a blank value in column F, but remove all others.</SPAN>

 </SPAN></SPAN>

Sub Row_Deletion_TEst()
    Dim ndx As Long
    Dim arrLike As Variant
    Dim Found As Range
    Dim ws As Worksheet
    
    arrLike = Array("CX-CD-LQ", "CX-CD-CB", "CX-CD-BB", "ZZ-ID-ID", "ZZ-ID-MA", "CX-NP-NC","CX-NP-NC","CST1","CST2")
    
    Application.ScreenUpdating = False
    
    For Each ws In Sheets(Array("Sheet1"))
          
        For ndx = LBound(arrLike) To UBound(arrLike)
        
            Do
                Set Found = ws.Columns("F").Find(arrLike(ndx), LookAt:=xlPart, MatchCase:=False)
                
                If Not Found Is Nothing Then Found.EntireRow.Delete
                
            Loop Until Found Is Nothing
            
        Next ndx
        
           
    Next ws
    
    Application.ScreenUpdating = True
    
    MsgBox "Row Delete Series Complete!"
End Sub

Thoughts?
</SPAN>
Thanks,</SPAN>
Allen </SPAN>

 

[Warship]

maybe...

     If Not found Is Nothing Then
        If found.Value <> "" Or _
          found.Value <> "criteria1" Or _
          found.Value <> "criteria2" Or _
          found.Value <> "criteria3" Then
            found.EntireRow.Delete
        End If
    End If

 

[ultracyclist]

I tried the following using the code provided, but it generates an error on the first line "If Not found Is Nothing Then"
Sub TestDelete()
If Not found Is Nothing Then
If found.Value <> "" Or _
found.Value <> "IL-CD-MB" Or _
found.Value <> "IL-CD-LB" Then
found.EntireRow.Delete
End If
End If
End Sub


The error message is a run time error 424 Object Required

 

[Warship]

I didn't specify...

Use your existing code but replace

If Not Found Is Nothing Then Found.EntireRow.Delete

with the code I posted earlier.