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