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.

 

[overbet]

Duplicate post

 

[overbet]

I manually selected and copied the code and it included the VBA: I think that messed it up. I just tried again and used the copy code button on the interface and it left out the VBA:. I ran the code again this way and got no error.

 

[Fluff]

Ok, this will probably need some tweaking, as I've got nothing to test on, but try

Sub overbet()
   Dim Ary As Variant
   Dim r As Long, c As Long
   
   Ary = Range("A1").CurrentRegion.Value2
   
   With CreateObject("scripting.dictionary")
      For c = 1 To UBound(Ary, 2)
         For r = 1 To UBound(Ary)
            If Ary(r, c) <> "" Then
               If Not .Exists(Ary(r, c)) Then
                  .Add Ary(r, c), Array(r, c)
               Else
                  If .Item(Ary(r, c))(1) > 5 Then Ary(.Item(Ary(r, c))(0), .Item(Ary(r, c)(1))) = ""
                  Ary(r, c) = ""
               End If
            End If
         Next r
      Next c
   End With
   Range("A1").CurrentRegion.Value = Ary
End Sub

 

[overbet]

I get an error. Screen shots attached. Do i need to add a reference or select the range?

 

[Fluff]

Oops, got one of the closing brackets in the wrong place, that line should be

                  If .Item(Ary(r, c))(1) > 5 Then Ary(.Item(Ary(r, c))(0), .Item(Ary(r, c))(1)) = ""

 

[overbet]

Wow that worked from what I can see it removed all the duplicates. Thank so much for your time and effort. I really appreciate it. You saved me a ton of time, eye strain and oatmeal brain Lol.

 

[Fluff]

Glad to help & thanks for the feedback.