Delete a group of rows with a condition

[ducky6]

Hi, everybody
I use this macro to delete rows with a lower amount compared to the one specified by the user:

Please Login or Register  to view this content.

Now I need something more. Let's assume that those amounts are related to some client: I have to delete ALL the rows related to a client, if none of them reach the amount specified by the user.

For example:
Client A - 25
Client A - 29
Client A - 35
Client B - 28
Client B - 26
Client B - 29
If the limit is 30, I must keep all of rows for Client A, and delete all of rows for Client B.

I hope my explanation was quite clear...
Any help is welcome,
thank you in advance!

[pike]

hi ducky6
Why use code when Advance filter will do the trick.

[ducky6]

Hi, Pike
thanks for your reply.

I'm preparing some macro for people that are not Excel experts and requested me something automatic, that's why. Anyway I've already done most of the work...

I'm trying to use a boolean variable inside the For loop... if the limit was reached by at least one row of a client, go back (with a While Do?) and delete all of rows for that client. Otherwise continue without deleting anything.
But seems like I have some problem with nested loops...

Thank you

[ducky6]

This is the code that I wrote so far:

Please Login or Register  to view this content.

but it doesn't work... it doesn't delete any row and the message box appears almost immediately.
Where am I wrong?

Thank you

EDIT: the variable limReached never changes to True, even when the limit is reached?

[pike]

Hi ducky6,
so any customer that has one value about the input value , has all records saved even it one is below the input value?

[ducky6]

Hi ducky6,
so any customer that has one value about the input value , has all records saved even it one is below the input value?

Yes, that's right!
It doesn't matter how many values (related to a customer) reach the limit, but if at least one row reach it or not.
Limit reached --> keep all his records
Limit not reached --> delete all his records

This control must be done for all of customers.

Thank you very much for your assistance!

[pike]

can you attach a sample worbook
have to loop though list and test names , then add them to collection then delete if not in the list

[ducky6]

Sorry for late reply,
please find attached a sample file with the macro that I'm trying to use.

Of course the original file is much more complex, so I made a new one with simple data.

I guess something is wrong with nested loops...
Thank you very much

[davesexcel]

Here is one way, it needs a couple of helper columns though.

[pike]

not as sweat as daves
but different

Please Login or Register  to view this content.

[ducky6]

First of all, thank you both!

I checked your solutions and of course they work perfectly. This is exactly what I was trying to do.
Both use helper columns, but it shouldn't be a big deal.

I hope this topic will be useful for other users on this Forum.

Thank you again!

P.S. I just have a question for Daves: what RC[3], R1C5, C[1], RC[1], C[-1] are exactly?
I assume they are references to the columns, but how can I change them if I need to refer to other columns? For example: AA and AB as helper columns, B as client and V as amount?
Sorry but I'm really a newbie at macro...

[davesexcel]

Try this amended code, it is alot faster than the first example by not using the loop.

Please Login or Register  to view this content.

[ducky6]

Thank you so much Daves.
Yes, this code is even quicker than the previous one

But to tell you the truth, I don't understand yet how to changes the references (RC[3]:RC[3], R1C5, C[1], RC[1], C[-1]) to other columns...

In my "real" file, AA and AB are supposed to be the helper columns, B is the client and V the amount. How can I refer to those columns?

Thank you

[davesexcel]

Maybe attach a sample workbook that shows your real layout.