Delete Duplicate Rows - Mult. Col.

[taya]

Hello All,

I have been searching through out the forums and found countless threads on deleting duplicate rows. The main issue is that most of these are for just 2 columns. I am trying to formulate a macro to compare several columns and rows. I want to have the macro look through the data and if all cells in the rows (not just ‘A and B’ or ‘C and D’) are the same it will delete the duplicates leaving just one unique row of that data.

I have been playing with the code from these two post but am still unable to get it to work for multiple columns.

http://www.excelforum.com/excel-prog...-row-rows.html

http://www.excelforum.com/excel-prog...-only-one.html

Thank you for any help,
Tay

[Leith Ross]

Hello Taya,

It would be easier to code a solution using your workbook. Attach the workbook with your next post.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[taya]

Hello Leith Ross,

Thank you for taking a look at this. This is a mock of my data. The first sheet has all the data (for the sample) and the second sheet has what I hope to get out of the macro. This sheet has only unique rows of data. Again the duplicates are deleted but one row of the duplicates is left.

Let me know if you would like any further explanation.

Thank you for your time,
Tay

[aclawson]

I wrote this a couple of years ago. It has worked very well for me, but has two basic assumptions: You must have either the top-left-most cell of your data table or the enitre data table selected, and each duplicate line must be adjacent to each other in the table.

When the user form appears, you can select your outcome from the drop-down list and click continue.

[taya]

aclawson,

Thank you for the input, the document you provided is very interesting and has very nice features for analyzing data. But the only issue is that my duplicates are not adjacent to each other. The real document I am working with has thousands of lines of data, in addition to several columns.

Thank you for the help aclawson, leith ross, and anyone else.
Tay

[whatsmyname]

Aclawson,

I am messing with your code a littl bit. It's really nice. I am looking for somrthing very similar to this, but I was curious if I can just select 1 column of data. Then when it deletes the duplicates it will delete the whole row not just those cells. Any ideas? Also, Instead of deleting, I would love it if it hid the rows of duplicates instead of deleting. Any ideas on that as well?

Thank you.

[aclawson]

wmn,

Sorry for the slow response. I typically get very busy at work the first half of the month. Your question is very doable, but would take some code modifications, and perhaps an additional field in the userform, thus preserving the multi-outcome functionality of the macro code.

Until I can re-visit the code at that level, though, a simple code to accomplish your qustion would be:

Please Login or Register  to view this content.