Delete Rows Not Containing Array Term

[dj44]

Good Sunday,

The below should work , but i dont know why

Delete all the rows not containing the array words

Sub Delete_Rows()
    
    
    
    oSearch = Array("Apple", "Pear")

    For i = LBound(oSearch) To UBound(oSearch)
    

    
    For Each ocell In ThisWorkbook.Worksheets("AA").Range("A1:A500").Cells
    
    If Not InStr(1, ocell.Value, oSearch(i)) > 0 Then
    

    ocell.EntireRow.Delete
    
    End If
    
    
    Next ocell
    Next i
  

  End Sub

Please do spot my error

[YasserKhalil]

Hello
Try looping reverse in case of deleting rows

Sub Delete_Rows()
    oSearch = Array("Apple", "Pear")
     
    For i = LBound(oSearch) To UBound(oSearch)
        For r = 500 To 1 Step -1
        'For Each ocell In ThisWorkbook.Worksheets("AA").Range("A1:A500").Cells
             
            'If Not InStr(1, ocell.Value, oSearch(i)) > 0 Then
            If Not InStr(1, Cells(r, 1).Value, oSearch(i)) > 0 Then
                 
                'ocell.EntireRow.Delete
                 Cells(r, 1).EntireRow.Delete
            End If
        Next r
        'Next ocell
    Next i
End Sub

[mana]

Option Explicit

Sub test()
    Dim a
    Dim c As Range
    Dim u As Range
    Dim i As Long
    Dim flg As Boolean
    
    a = Array("Apple", "Pear")
         
    For Each c In ThisWorkbook.Worksheets("AA").Range("A1:A500")
        flg = True
        For i = LBound(a) To UBound(a)
            If InStr(c.Value, a(i)) > 0 Then
                 flg = False
                 Exit For
            End If
        Next
        If flg Then
            If u Is Nothing Then
                Set u = c
            Else
                Set u = Union(u, c)
            End If
        End If
    Next
    
    If Not u Is Nothing Then u.EntireRow.Delete
     
End Sub

マナ

[dj44]

thank you Gentlemen for your help,


@Yasser - I was not able to get it to delete the rows



@Mana - Thank you for the new way of writing the loop, I have not used the Union before, and it deleted the rows perfectly.


Thank you for your help and good sunday!

[snb]

Sub M_snb() 
 for each it in Array("Apple", "Pear") 
   columns(1).replace "*" & it & "*",""
 next

  on error resume next
  usedrange.columns(1).specialcells(4).entirerow.delete
End Sub