Find and Delete full row macro (with exceptions)

R

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.
 
B

Bob Umlas, Excel MVP

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
 
R

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
 
R

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
 
T

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.
 
R

Rubix³

Wow. Thanks Tom.

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

:
 
Top