Find and Delete full row macro (with exceptions)
 

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.


---
Message posted from http://www.ExcelForum.com/

Find and Delete full row macro (with exceptions)
 

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³ " wrote:

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.


---
Message posted from http://www.ExcelForum.com/

Find and Delete full row macro (with exceptions)
 

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

--
Message posted from http://www.ExcelForum.com

Find and Delete full row macro (with exceptions)
 

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

--
Message posted from http://www.ExcelForum.com

Find and Delete full row macro (with exceptions)
 

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.

--
Regards,
Tom Ogilvy


"Rubix³ " wrote in message
...
Quick question -

Is there a way to implement this so that it can keep the "APO" lines if
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 to
keep.

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

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

Thanks for reading.


---
Message posted from http://www.ExcelForum.com/

Find and Delete full row macro (with exceptions)
 

Wow. Thanks Tom.

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

:

--
Message posted from http://www.ExcelForum.com