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
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
Last edited by Burt_100; 09-30-2016 at 04:22 AM.
Try this macro on a copy of your file:![]()
Please Login or Register to view this content.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
Burt_100,
xl2007 should accepts array criteria.
![]()
Please Login or Register to view this content.
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?
When the range is filtered, the range only refer to visible rows, so offset(1) will exclude header row.
Help?
That is very helpful. Thank you. In the macro I suggested, I usedIt 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?![]()
Please Login or Register to view this content.
That's correct, only when filtered.
Thank you so much for all your help and patience. It was very helpful.![]()
Thanks for your help but the code only seams to delete a few of the entries and not all of them?
I have tried my macro and the one jindon suggested on your sample file and both macros seemed to work properly.
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
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.
Hi,
See attached file as requested filename; "Example2".
Many Thanks
Johnny
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".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"
How does this work for you?
![]()
Please Login or Register to view this content.
Last edited by bakerman2; 10-04-2016 at 06:42 AM.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
It looks like bakerma2 has given you the solution.
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
Can anyone help with this?
Can anyone help with this?
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
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.
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.
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.
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.
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?
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.
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.
Thanks for adding rep points.![]()
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks