Link to home
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

Highlite duplicate in Excel

Hello all

I have a sheet where i need to put in Yellow all duplicate.

They are not sorted but it need to find them.

How can i do this without having to sort them also.

Thanks for your help.
Duplicate.xlsm
Avatar of Wilder1626
Wilder1626
Flag of Canada image

ASKER

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

Open 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

Open 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

Open 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

Open 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

Open in new window


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

Open 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
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Oh, this is so perfect.

Thank you so much for your help.