Macro Delete Duplicate Rows with Exception
I am creating a macro to delete rows based on a duplicate value in column E (see code below). However, there are times that I signify a blank cell with "--", in these instances I would like to not delete those rows because there is other information that is needed. Thanks for your help in advance.
Please Login or Register to view this content.
Last edited by arlu1201; 06-15-2012 at 10:57 AM. Reason: Code tags
Do you have a sample file that you can upload?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Yes. In the attached file the "Report Example" is what I want the finished product to look like. You will notice the "--" in Cell E9 and K8. Right now I have written the code (see first post) that will go through and delete duplicate rows (based off information in column E or K). In the "Report Example" since E9 and E12 are the same ("--") then the second row will be deleted. I would like to modifiy the EA to avoid deleting duplicate "--".
Thanks for your help.
Based on your code example and attachment, I'm following what you want to do based on Column E.
What do you mean by "delete duplicate rows (based off information in column E or K)"?
How does column K factor into the criteria?
The macro will look in the through the different worksheets for specific information that will be copied in to either Column E or Column K. The code so far is just for Column E since I will just need to expand the code, or copy it for Column K when it is working.
Let's set aside the issue of the excluding the "--" cells, which I understand.
Are you saying the you will delete rows with duplicates in Column E, then delete rows with duplicates in Column K as two separate steps?
Or are you only deleting rows that have duplicates of Column E plus Column K values on the same row?
The code for those two criteria would be different.
I am deleting rows will duplicates in Column E, then in the next step delete duplicates in Column K.
I think I figured out a way to use advance filter to get the result I want. However, when I recorded the macro and tried to run it with a buttonclick, I keep getting an error. Doing a quick search I have seen this problem come up but have not seen any workable solutions for me.
The error is Run-time error '1004' - Paste method of Worksheet class failed
Any thoughts?
Please Login or Register to view this content.
Using AdvancedFilter is a good way to remove duplicates for xl2003; however your added condition of keeping all "--" rows, makes that more difficult. To do this with AdvancedFilter, you would probably need to have two filter-copy steps or use a formula-based criteria instead with Unique=False.
Here is some code you could try that uses a temporary column of formulas to mark those to be deleted.
This just looks for duplicates in Column E. You could repeat the process for Column K, or try to modify the formula to do it in one pass.Please Login or Register to view this content.
Last edited by JS411; 06-18-2012 at 04:34 PM.
The advance filter would is actually providing the correct end results. The reason I did not want to delete the "--" in either column was because there could be a value in the other column. If I can get the advanced filter working within the macro and keeping the unique rows, then the goal would be accomplished.
Any thoughts on the error message with the paste portion?
The error message is because the range that was copied is no longer available to be pasted through VBA after executing the ActiveSheet.ShowAllData statement.
One workaround is to paste the Unique Values to a temporary location.
Here's some code that temporarily pastes the values below the data
Please Login or Register to view this content.
Last edited by JS411; 06-20-2012 at 12:42 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks