Keep Highlight Duplicate values shading

[overbet]

Hi,
I was wondering if anyone had any idea how accomplish this task. I have a large sheet with a lot of duplicate values Id like to remove. I used conditional formatting to highlight the dupes, but the problem is when I delete one it takes Excel a good minute to respond. There are about 1000 dupes so this is too painfully slow to do this way. I have tried turning calculation to manual which helps, but not enough its still too slow. I have tried creating a duplicate sheet -2 and highlighting them on one while deleting on the other. This also helped, but gets confusing because the highlights never go away on the -2 sheet. I would like to know if I can copy and paste the conditional formatting format that highlights them pink and then remove the conditional formatting to be left with the dupes highlighted. Maybe there is some way to sort by the conditional formatting results then i could cluster them and delete all at once? Or if someone has another possible solution to speed things up. Appreciate any help.

 

[Fluff]

Are you trying to delete all duplicates, or do you need to keep one of them?
Also what determines if it's a duplicate?

 

[overbet]

Hi, I am trying to keep one instance and needing to keeping the instance if it exist in columns A,B,C or D.

cell b29: ABC
cell c120: DEF
cell byl88: BCD
cell axl42: CDE
cell ckx150: ABC

In this example Id like to remove the ABC in cell ckx150, but keep the ABC in cell b29. If the duplicate value is in either columns A,B,C,D,E. Keep that single instance. If it is a duplicate anywhere else remove it. The data goes all the way to Column CKX ? Hope that makes sense. Ive been doing it manually and waiting for the lag, its quite a tedious task.

 

[Fluff]

Do you have any formulae, or formatting that needs to be kept?

 

[overbet]

Do you have any formulae, or formatting that needs to be kept?

No its just plain text data no formulas or formatting.

 

[Fluff]

Assuming that you have no completely blank rows/columns in your data, do you get an error if you run this?

Sub chk()
   Dim Ary As Variant
   Ary = Range("A1").CurrentRegion.Value2
End Sub

 

[overbet]

I do get an error. There are no blank cells from left to right, but there are blanks vertically. I did a sample and attached an image. The sheet takes alias IDs and converts them to a primary ID in column A so some IDs may have more or less aliases than others. In the example I attached ABC would be the primary ID, but ABC.1 & ABC-1 are aliases for the primary ID ABC. The alias are always sequential with no blanks from left to right.

 

[Fluff]

What error did you get?
Also what column can be used to find the last row?

 

[overbet]

What error did you get?
Also what column can be used to find the last row?

Compile Error: Invalid outside procedure.
There are 2000 rows and the last Column is CKX

 

[Fluff]

Compile Error: Invalid outside procedure.

That suggests that you did not copy the entire code.