Delete Rows based on criteria in two columns

Here is what I am looking to do:

In the attached form, delete all "containers" that have bananas and/or apples in them. So, in this case, container "h" would be the only container left.

This would need to be variable, because the row locations are subject to change, and I will have to do this on a daily basis. The Product and Section would always be in the same columns. and I always want to delete containers with bananas and/or apples in them. But the bananas could show up on A4 on one day, and A12, the next. And the container names will also change, from one day to the next.

The solution could be a formula I copy down, some fantastically advanced Filter function, or a macro. Any help would be appreciated. Thanks.

Fruit.xlsx

Try the attached code, button on sheet

Thanks. That was a good array. However, the entire container was not deleted, only the actual rows with the entry of banana and apple. For instance, all rows of container "C" should have been deleted, because there was a banana in the container.

Fruit Results.xlsx

The code deletes the entire row. The c you have in your sample do not have banana or apple, but this line is also the same

Please Login or Register  to view this content.

The issue is that the other rows associated with that one container are left in the spreadsheet. So, maybe look at it as the apples and bananas contain contaminants. I have to get rid of any container which houses a banana or apple, because they've contaminated all of the goods in the entire container.

Maybe something closer to:

DIM n as apple, banana

If Range ("A2").Value = n Then C1.value = w
For each w entirerow.delete

While I'm sure my alterations border on the ridiculous, I was able to successfully tweak swolfe2's code to match my case
http://www.excelforum.com/excel-prog...med-range.html

Please Login or Register  to view this content.

This is a different question to the one you had asked. The code you posted is using dictionary object to compare two columns, while the original thread was about deleting a row if there is banana, or apple on it. Got the solution you are looking for and that is good.

The request was to delete any rows associated with a container which housed an apple or banana. One apple or banana taints the entire container, so the entire container has to go. The container names are listed in column B as "c, h, n, l, p, and o". Because Container "c" housed an apple, banana, vanilla, and pears, it used up four rows. All four rows would have to be deleted.

I don't actually know what dictionary object is. I merely spliced together four random scripts from The Internet, and it worked beautifully (and quickly, considering the wordiness of it).

However, if someone were looking to delete only rows with an apple or banana, I'd probably do something like this (because I love arrays):

Please Login or Register  to view this content.