I have a good macro to delete duplicates that is very efficient and fast, At the moment it keeps the first line, but I want it to keep the last line instead. Any help is appreciated. Its a good macro if anyone else wants to use it. Here is the current macro.
Cheers
Please Login or Register to view this content.
Rain
Last edited by rain4u; 02-08-2011 at 07:51 PM.
try to change this line
to thisPlease Login or Register to view this content.
Please Login or Register to view this content.
Everything remained exactly the same. It still keeps the firs line. Any more ideas???
I would think it would be:
Please Login or Register to view this content.
It would much simpler to tell you for sure with a small sample file of 10-20 rows with before/after examples of what you're wanting.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
So far no luck with any offered solutions.Yeah no probs. Here is the file. Please highlight the column A and run the macro.
Only way to make sure which line it really did keep is by looking column K. The only row left should be with value -4
Its the last line in my test sheet.
Please advise what to try next. I'm bit desperate to get it for tomorrow. Have to sort out some reports. Cheers
Last edited by rain4u; 02-08-2011 at 06:35 PM. Reason: typos
from you file: why should 4 and its row be left? As for me the code should leave -7, -6, -5 and 4 and with offered solution (post above) it does it
This is how I would do that:
Please Login or Register to view this content.
This version will work based on the current activecell's column:
Please Login or Register to view this content.
Last edited by JBeaucaire; 02-08-2011 at 07:09 PM.
Thank you Sir! works like a charm.Originally Posted by JBeaucaire
As you are so brilliant I have another one for you. You can check that with the same xls.
Can you give me a macro of conditional formating that would highlight every duplicate as per column A. And I mean every cell in coll A (that includes all the duplicates + one unique). The formulas that I have managed to find on my own initiative a will always keep one cell (either the first cell or the last cell in column) not highlighted. They do that as they consider it unique. I would like that to be highlighted as well. Its just in my reports there could be 3 that are basically the same. I have to work some reports line by line. The order is always by date and im after some parts. The formulas so far will skip one. But i would like to include that please. On my spread sheet every single one should be highlighted on Col A
Can you help?
Actually I will close the thread and open a new one Cheers for everybody who contributed. Thank you
Kind regards
Rain
INCLUDED:
-- Leave top dupe OR Leave bottom Dupe FROM user msg click
-- error check for single column selection
-- msg confirmation of currect row selected
Please Login or Register to view this content.
Hi guys
I have used this code for a some time now
But now I need to use column B instead of column A. I tried to do some amendments to the code like the obvious ""A" & Rows.Count" to "B" & Rows. Count but it think i get it wrong and Im missing something as when running the macro it prompts me with error "No cells were found"Please Login or Register to view this content.
Can someone point me to a right direction. Any help would be great.
Have you tried the code I've provided ? It allows the user to select a column to base the "dupe check" on.
In the R1C1 syntax, C1 means "column 1" or "column A". You need to change those references to C2 for column 2 / column B.
Please Login or Register to view this content.
Hi Nimrod and JBeaucaire
JBeaucaire, the code now works again. Thank you for that
Nimrod, Can you please help me to amend your offered code to work without prompting an option to a user of which row to keep. I would be really grateful to have both examples but if you don't ave much time then simply to keep the bottom one would make me happy. Simple reason is that after a test run I realized that your code works brilliantly. I timed both of the codes. The usual code I have used so far takes about 18 seconds with my data. Your code does it within 3 seconds. So can you help me to remove option for the user?
Cheers
Replace all this ...
Please Login or Register to view this content.
With this ...
Please Login or Register to view this content.
Thank you Nimrod. It works very efficiently.
Interestingly if you remove this section above all together it acts as "no". Just for the as cautionary I have still just replaced with the line as advsied by nimrod.Please Login or Register to view this content.
How to keep the top duplicate? I have tried the obvious by changing "no" to "yes". It does not work.
Cheers
Rain
Hi
Watersev
Can you have a look of the attachment.
Cheers
Rain
hi, rain4u, please check attachment, run code "test"
Cheers watersev. Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks