How to delete ALL duplicates cells

Hi

How to delete all duplicates in active column with excel VBA ?

For example if i have 3 duplicate cells then delete all 3 cells.
( Don't leave one cell and delete the other two cells ).

For example Please see the attached file before and after sheets

Thank You.test.xlsx

Maybe:

Please Login or Register  to view this content.

Thank you, is working on the test file...

but i have 150K records and a getting "not responding"

I tried the two codes

You should run the code from the active sheet, i.e. before sheet on the attached sample.

I tried the attachment

I getting a "not responding" after adding 150k records and run the macro "testdeldup"

try

Please Login or Register  to view this content.

I have tried it on half millions rows and was not slow.
It is difficult to comment on your data with out seeing it.
Are these data imported in to excel from other application, like Oracle Finance or SAP?

Yes. I pull the data from sql server to Excel via "Excel connection"

BUT I copy the column and paste as a text to a new Excel file.

BUT I copy the column and paste as a text to a new Excel file.[/QUOTE]
Does your PC make noises while the code is running? It is because the data are consuming lots of PC's memories.
Well, this is known problem as imported data have unseen dirty characters.
Copying the data again, but when you paste them paste the data as VALUES ONLY will get rid off some of the dirty data, but not entirely. Delete the old workbook after you saved the newly copied data.
You could run a separate code which cleans the data, but that is another topic.

I found the solution, I did a sort to the column, and then deleted all the empty cells and then the macro worked Good

Thank You!