Delete all occurrences when parameter has been met.

[Burt_100]

Hi,
I am looking code that will delete all occurrences of a record "Key Field" in column "E" if the anyone of the following parameters are in column "A", "D51", "D54", "D55", "D56", "D58", Can anyone help me with this?
Thanks
in advance.
Johnny

[Mumps1]

Try this macro on a copy of your file:

Please Login or Register  to view this content.

[jindon]

Burt_100,

xl2007 should accepts array criteria.

Please Login or Register  to view this content.

[Mumps1]

Hello jindon. That's a very interesting approach. Would you mind explaining how the ".Offset(1).ClearContents" works? How does the macro delete only the visible cells?

[jindon]

When the range is filtered, the range only refer to visible rows, so offset(1) will exclude header row.
Help?

[Mumps1]

That is very helpful. Thank you. In the macro I suggested, I used

Please Login or Register  to view this content.

It looks like

Please Login or Register  to view this content.

would also work without referring to the visible cells. I suppose that this would work on any action you want to perform on the visible range, for example

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Is this true?

[jindon]

That's correct, only when filtered.

[Mumps1]

Thank you so much for all your help and patience. It was very helpful.

[Burt_100]

Thanks for your help but the code only seams to delete a few of the entries and not all of them?

[Mumps1]

I have tried my macro and the one jindon suggested on your sample file and both macros seemed to work properly.

[Burt_100]

They work but they don't delete all instances for some reason even when I download the example file I uploaded and copied the macro in

[Mumps1]

Can you attach a copy of the file that is not working properly. Highlight in red the instances that are not deleted after running the macro.

[Burt_100]

Hi,
See attached file as requested filename; "Example2".
Many Thanks
Johnny

[Mumps1]

In your original post you asked to

delete all occurrences of a record "Key Field" in column "E" if the anyone of the following parameters are in column "A", "D51", "D54", "D55", "D56", "D58"

. The macro does exactly that. The file you posted has no data in column E where column A contains "D51", "D54", "D55", "D56", "D58".

[Burt_100]

In your original post you asked to. The macro does exactly that. The file you posted has no data in column E where column A contains "D51", "D54", "D55", "D56", "D58".

Sorry I understand the confusion now, I need the entire row deleted not just the key field in column "E", sorry its totally my fault for not being clear.

[bakerman2]

How does this work for you?

Please Login or Register  to view this content.

[Mumps1]

It looks like bakerma2 has given you the solution.

[Burt_100]

Works great but have found another problem sorry, I need the code to delete all earlier occurrences of the key field (row) if the parameter "D51", "D54", "D55", "D56", "D58" is met

[Burt_100]

Can anyone help with this?

[Burt_100]

Can anyone help with this?

[jindon]

I need the code to delete all earlier occurrences of the key field (row) if the parameter "D51", "D54", "D55", "D56", "D58" is met

What do you mean?

[Burt_100]

I mean that once a parameter has been met not only does it delete that occurrence but also all other occurrences with the same key field in column E. Hope this is clear enough

[jindon]

Column E?
Both of your files uploaded has no matched data in Col.E.

You should upload a file with your desired result clearly showing how EXACTLY you want.

[Burt_100]

See attached file "Example_all", I have highlighted a few rows which meet the criteria and should be deleted, Hope this clears things up.
Many Thanks
Johnny

Column E?
Both of your files uploaded has no matched data in Col.E.

You should upload a file with your desired result clearly showing how EXACTLY you want.

[jindon]

I need the code to delete all earlier occurrences of the key field (row) if the parameter "D51", "D54", "D55", "D56", "D58" is met

Again, there's no matched row(s) in Col.E.

[bakerman2]

In your earlier example file (example2) all cells in column E with the matching parameter in column A were empty so there was nothing to match in column E.
So this whole Column E thing is a new ball game which you will have to explain further because right now we have not a single clue (at least I don't) what you are trying to achieve.

[Burt_100]

Sorry for the confusion, what I need is to do is if any of the parameters D51", "D54", "D55", "D56", "D58 are met in column A then I need the code to search all of column E and delete any occurrences (row) of the same key field.

In simple terms once the parameter has been met I want to delete all occurrences associated with that field.

[Burt_100]

Sorry for the confusion, what I need is to do is if any of the parameters D51", "D54", "D55", "D56", "D58 are met in column A then I need the code to search all of column E and delete any occurrences (row) of the same key field.

In simple terms once the parameter has been met I want to delete all occurrences associated with that field.

[bakerman2]

So what it comes down to is to create a unique list of all occurences in column E that meets one of the parameters.
Then using that unique list filter column E on all of the elements of that list and delete all the rows accordingly, disregarding the value in column A.
Does that describe it a bit?

[jindon]

Sorry for the confusion, what I need is to do is if any of the parameters D51", "D54", "D55", "D56", "D58 are met in column A then I need the code to search all of column E and delete any occurrences (row) of the same key field.

In simple terms once the parameter has been met I want to delete all occurrences associated with that field.

You mean check the content of col.E where col.A meat the criteria then delete all the rows that have same col.E content regardless of Col.A?
If so

Please Login or Register  to view this content.

[jindon]

Burt_100

Received rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[bakerman2]

Thanks for adding rep points.