Highlite duplicate in Excel

I have done the duplicate hightlite now like this:

Sub Highlight_Duplicates(Values As Range)
Dim Cell

For Each Cell In Values
    If WorksheetFunction.CountIf(Values, Cell.Value) > 1 Then
        Cell.Interior.ColorIndex = 6
    End If

Next Cell
End Sub



Private Sub CommandButton1_Click()
Highlight_Duplicates (Sheets("Feuil1").Range("V7:V3000"))
End Sub

Select allOpen in new window

I would like now to create a macro that would delete only the doubles ans leave only the single one. So it if found a double, it will leave one in there and that macro will delete the second record.

Select the column, then go to Conditional Formatting, highlight cell rules - and find the word DUPLICATE.

Color / Shade, etc., as you like !

Enjoy!

Dave

To delete duplicates, select the column, then from the Ribbon, select Data - look at Data Tools, and Remove Duplicates...  It will keep the first occurrance.

You can record macro then customize as desired.

Cheers,

Dave

Here's your HighlightDuplicates code - yours is just fine, but this may be faster:

ActiveSheet.Range("$V$7:$V$1333").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Select allOpen in new window

Here's your delete Duplicates code:
 

ActiveSheet.Range("$V$7:$V$1333").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Select allOpen in new window

This also, likely to be faster and pretty easy to modify for your modules...

Enjoy!

Dave

ActiveSheet.Range("$V$7:$V$1333").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Select allOpen in new window

I don't know what happened with my pasting.  Sorry about that!

Starting over:  HIGHLIGHT DUPLICATES

ActiveSheet.Range("$V$7:$V$1333").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Select allOpen in new window

DELETE DUPLICATES:
 

ActiveSheet.Range("$V$7:$V$1333").RemoveDuplicates Columns:=1, Header:=xlNo

Select allOpen in new window

Enjoy!

Dave

Thanks for all your help.

Can we delete the entire row of the duplicate ones? instead of just the cell?

Just specify the entire range for removal, and just the column to look for duplicates...


Does your data have headers?


Dave

Oh, this is so perfect.

Thank you so much for your help.