LinkBack URL
About LinkBacks
How do I delete duplicate rows in a sheet using a macro. When I say duplicate row, it is not based on a particular column but all the columns, so it is a true duplicate record.
How do I delete duplicate rows in a sheet using a macro. When I say duplicate row, it is not based on a particular column but all the columns, so it is a true duplicate record.
Hi,
One way would be to use a couple of helper columns. In one, say column D, concatenate all the column values for a row (i.e. =A1&B1&C1) and copy this down. Then in the other helper column say column E enter the formula
Now filter column E for values > 1. These are all the duplicate records. Now just select all these rows and delete them.
Please Login or Register to view this content.
HTH
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Richard's approach is how I would do it, too. My only caveat is you have to be aware of false matches.
If A1 = 22
B1 = 3
Then concatenation formula =A1&B1 results in the text string "223"
If A2 = 2
B2 = 23
Then concatenation formula =A2&B2 also results in the text string "223"
This is a false match.
While constructing your formula, by hand or by macro, consider putting a "spacer" between each field to keep the matches exact:
=A1&"-"&B1
...results in "22-3" and "2-23" so no false matches.
_________________
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!)
Thank you for the reply. But I would like to automate it. since # of columns are too many and might increse or decrease.
This macro assumes the first row is "titles" and should be skipped. It requires the first row for the AutoFilter, so if your data really does start at row 1, add a blank row there.
Please Login or Register to view this content.
Thanks for the code. I ran it and the all the data got deleted. I am working on draft dont worry. But wondering if I need to change somethign in the code
Last edited by DonkeyOte; 01-08-2010 at 05:13 AM. Reason: unnec. quote removed
Nope. Feel free to upload a sample sheet so I can see examples of your kind of data. It works fine on the sample sheets I have, so let's look at yours.
Oh, and don't use the QUOTE button to reply, it just clutters the thread and makes finding your responses harder...unless of course there is something specific you want to point out...quote just that part. Otherwise, just use the QUICK REPLY box below.
Thank you for your suggestions. Here is what I am doing. I have a macro which concatenated sheets(with name "test") from all workbooks in a folder. In this code I had to specify the range. So I specified from first label row too. Since these worksheets in all the workbooks have same structure including same header row, I am getting multiple header rows(duplicate records). So I need to keep just the first one and delete the test duplicate rows. Using your code, I did 2 ways:
1.Ran the code as is since the 1st row is header row(label row from the first source sheet). This way it is keeping the first one and deleting everything. SO finally I have only the first row.
2.i have inserted a blank 1st row and then ran your macro. Its deleting the duplicate header rows but it is deleting the the first one as well.
If I run the worksheet from where i actually run the code for concatenating worksheets from workbooks in a folder, it creates a new workbook with the concatenated sheet. When i run this macro in this new sheet immediately this is not working at all. If I save this sheet else where and run this code it is working as in my previous post
I'd really like to help. Since the macro is working for me on my test sheets, I'll need to see your sample workbook.
Click GO ADVANCED and use the paperclip icon to post up your workbook.
Hello JBeaucaire,
I tried to use your code below but found the macro struck at the line:
My comparision has to start from the 5th row.Please Login or Register to view this content.
I have changed ur code as follows:
Any help to fix it will be helpful.Please Login or Register to view this content.
Thanks innAdvance.
VIjay
If you find any of the post useful to your question, please add to the contributor's reputation by clicking the blue scales icon in the blue bar of the post.
The change you made is meaningless. It shouldn't change the way the LR and LC values are being determined.
Originally Posted by JBeaucaire
Thanks for your reply.
Have attached a sample workbook.
Yeah, your workbook has some null values stored all over the place that completely invalidates the .SpecialCells(xlCellTypeLastCell) syntax. So we'll use a different approach.
Also, your original request was to eliminate all duplicate rows, not just ONE of the duplicates, so I had to correct the logic on that, too.
Use these macros in replacement:
Please Login or Register to view this content.
Thanks JBeaucaire,
Will give it a try and get back soon.
Vijay
Thanks a lot JBeaucaire.
The macro did exactly what I wants.
Thanks once again.
Vijay
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)
Its not my post.
I have replied in this post.
I'am not able to edit the prefix.
Thanks,
Vijay
Which rather begs the question: why have you seen fit to ask your own questions in another member's thread ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks