View Full Version : [SOLVED:] Delete Rows Not Containing Array Term
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
11-26-2017, 04:11 AM
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
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
マナ
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!
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.