PDA

View Full Version : [SOLVED] Find strings and delete



remy988
09-20-2013, 08:24 AM
hi,

i have the following code that does work, but what is the better way to code this?


Sub DelTest()
lastrw = Cells(Rows.Count, "a").End(xlUp).Row
For i = lastrw To 1 Step -1
If Range("a" & i).Value Like "*APPLE*" Then Rows(i).Delete Shift:=xlUp: GoTo nexti:
If Range("a" & i).Value Like "*ORANGE*" Then Rows(i).Delete Shift:=xlUp: GoTo nexti:
If Range("a" & i).Value Like "*BEAR*" Then Rows(i).Delete Shift:=xlUp: GoTo nexti:
nexti:
Next i

End Sub

thanks,
rem

mancubus
09-20-2013, 12:29 PM
hi rem.

you can combine the conditions with OR operand,

or try this.



Sub Del_Rows_Based_On_Partial_Match_Multi_Crit()

Dim filtArr As Variant

Application.ScreenUpdating = False

filtArr = Array("=*APPLE*", "=*ORANGE*", "=*BEAR*")

With Range("A1").CurrentRegion
For i = LBound(filtArr) To UBound(filtArr)
.AutoFilter 1, filtArr(i)
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Parent.AutoFilterMode = False
Next
End With

End Sub

remy988
09-20-2013, 05:43 PM
thanks Mancubus, i'll use the OR operand. i could not compile your other code.


rem

snb
09-21-2013, 03:55 AM
sub M_snb()
for each it in array("orange","apple","pear")
columns(1).replace "*" & it & "*" ,""
next

columns(1).specialcells(4).entirerow.delete
End Sub