Highlite duplicate in Excel

Wilder1626
Wilder1626 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Most Valuable Expert 2012
Top Expert 2012

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

Color / Shade, etc., as you like !

Enjoy!

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Most Valuable Expert 2012
Top Expert 2012

Commented:
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?
Most Valuable Expert 2012
Top Expert 2012

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


Does your data have headers?


Dave
Most Valuable Expert 2012
Top Expert 2012
Commented:
Here's the code - note I commented the recorded code then wrote the macro:

 
Sub deleteDups()
Dim startRow As Long
Dim lastRow As Long
Dim colToCheck As Long
Dim rangeToCheck As Range

    startRow = 6
    lastRow = Range("V" & Rows.Count).End(xlUp).Row
    colToCheck = Columns("V").Column
    
    Set rangeToCheck = Range(Range("A" & startRow), Cells(lastRow, Columns.Count))
    
    rangeToCheck.RemoveDuplicates Columns:=colToCheck, Header:=xlYes
    
    'ActiveSheet.Range("$A$6", Cells(1333, Columns.Count)).RemoveDuplicates Columns:=22, Header:=xlYes
End Sub

Open in new window


See attached file - I made up some other data and added header to demonstrate deleting entire row...

Enjoy!

Dave
Duplicate-r1.xlsm
Oh, this is so perfect.

Thank you so much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial