How to efficiently delete entire rows based on duplicates in one column

Hello everyone!

Big conundrum here. I've got about 50000 rows and 10 columns of data. In one column (column J), I have a bunch of values that I want to reduce down to just unique values, but I want to delete the entire row for duplicates in this column, and do it with a macro! Problem is, I am running into HUGE inefficiencies with my current code, code inspired by certain suggestions I've found throughout the internet. Here's what I'm trying to do currently:

Please Login or Register  to view this content.

To explain further, I would like something like this:

A B C ... J
1 2 3 VAVA
4 5 6 VAVB
1 8 9 VAVA
1 2 3 VAVC
1 4 4 VAVB
1 4 4 VAVC

to go to this:

A B C ... J
1 2 3 VAVA
4 5 6 VAVB
1 2 3 VAVC

Just keeping the unique values in column J and deleting entire rows for duplicates

I got the dictionary idea and variations on the for loop idea from stackoverflow, but these methods are EXTREMELY slow and inefficient when working with 50,000 rows of data. I swear there has to be a way to implement built in "removeduplicate" functionality with VBA. Any ideas? Thanks everyone.

Do you mean?

Please Login or Register  to view this content.

You can also use advance filter to copy unique values only.

just for interest, if you want to pursue the dictionary/loop approach, you can try something like this, which doesn't take very long to execute

Please Login or Register  to view this content.

as noted, there is a Remove Duplicates facility in Excel, but only from 2007 version onwards.

if using it, you might consider a couple of points, for example, do you regard 1.0 and 1.00 as duplicates (or not) in the same way as the Excel remove duplicates does?

also the Remove Duplicates has been noted to have a possible bug, in that there's datasets around from which it doesn't remove duplicates. hence for complex datasets and important applications,it's not a bad idea to have an independent verification method to check the Excel Remove Duplicates results.