Find and Delete full row macro (with exceptions)

[Rubix³]

Hey all

I've done "find and delete entire row based on value of a cell," but
the solution to this latest mess I'm in eludes me. I've tried a dozen
different techniques (not kidding).

Anyways, here's the deal. In Col B, I have different "issue types."
Stuff like, "administrative," "image," and " Service ."

When "Service" is present in COL B, I have to note the value in the
same row, COL D. If this value is "APO," then I KEEP it. Anything else,
delete.

In other words, all instances of "service" in COL B that DO NOT have
the value "APO" in column D, delete entirerow.

Thanks for reading.

 

[Guest]

Try this:
Sub DeleteServiceNotAPO()
For i=Range("B65536").end(xlup).row to 1 step -1
If cells(i,1).value="service" and cells(i,4).value<>"APO" then
rows(i).Delete
End If
Next
End Sub

 

[Rubix³]

Worked like an absolute charm. Good work.

Quick note: probably a typo, but "If Cells (i,1)" should be "If Cell
(i,2)" in my case.

Thanks Bob Umlas. :cool

 

[Rubix³]

Quick question -

Is there a way to implement this so that it can keep the "APO" lines i
they don't EXACTLY contain "APO?" - ie: Instead of "APO" in COL D
there's "APO Contra," "APO blonde ale," etc. All of which I'd like t
keep.

Is there a wildcard command that says "if anywhere in the cell the tex
APO is found, keep it?"

I'm just trying to cover all angles in case the user enters somethin
other than just "APO."

Thanks for reading

 

[Tom Ogilvy]

Sub DeleteServiceNotAPO()
For i=Range("B65536").end(xlup).row to 1 step -1
If cells(i,1).value="service" and Instr(1,cells(i,4).value,"APO", _
vbTextCompare) = 0 then
rows(i).Delete
End If
Next
End Sub

This would not delete if the cell contained "capote" for example. You
might want to make the check more stringent.

 

[Rubix³]

Wow. Thanks Tom.

Again, both codes work like a charm. Thank you both for your help.

: