2 columns find dublicates and delete them with macro

No!
Data is partially too different or not the same. Not easy (impossible) to create same rows with that much data. At least for me.

I invented a "childrens"-method how to make it.

- I make 4 columns out of 2. --> AB and again AB
- Then I color the fonts differently in every column --> blue, red, green, pink
- Then I mix AB in one column and other AB in second column
- Then I sort first column alphabtically and the other too.
- Then I delete in both columns the doubles one after another
- Then I sort colors again and delete in both columns the "shorter color" and the "longer color"

Yes of course, my fault. I expanded 2 lines to B48000. And of course without marking it red and after that deleting red. I want it to be deleted ad hoc. Without marking it red it still needs a lot of time. But I guess it's the way it has to be.

Please Login or Register  to view this content.

What I found when testing my macro setup was that I had to do it in a two step process, first mark all duplicate cells and then clear/delete the marked cells in order to get rid of all duplicates.

As the number of rows you are checking is rather huge you could probably use autofilter for each column as a stand alone for the second part instead of looping through the range. But then you need to set the same condition on the all duplicate cells in order to know what to filter for.

And of course without marking it red and after that deleting red. I want it to be deleted ad hoc

Not sure that is possible i.e. I could not make it work but perhaps another forum member has a better idea.

Testing your modification of my code gives this result by the way.

aa_del.jpg

Alf

Hello pollux7,

Please refer to your Post # 10, and note that your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window.

More information about these and other tags can be found here

(Please do this before posting any further.

In the meantime, you may want to try;

In a Standard Module, enter the Code as below;

Please Login or Register  to view this content.

Now before you run the Code, ensure you have a valid Backup of your Data in Sheet2.

Regards.

@ Logit,

I won't even apologize for the delayed response!

First I had to go outside to speak "Sailor" and kick the dog, and sniff some dope to calm down. So sorry you were not the Dog, poor animal!

My interpretation of the OP's requirement is that there should NOT be two of anything in the Columns combined. Your feeble attempt leaves two 13's and two 15's remaining after your "wonderful", shall we say Coding?

Oh MY!

@ Logit,

Hey Buddy,

Please look at Posts 1 through to 5 again. That explains my interpretation, or what am I missing?

Why do you not delete no's 13 and 15 as well?

Just wondering.

Regards.

@ Logit,

I forgot to mention that one of each of the following no's should remain, and not be cleared; No's. 1, 5, 8, and 9.