Delete 1 of the duplicates from excel

If you are using one of the newer versions of Excel you don't need a macro, there is a Remove Duplicates function in the Data tab

Try this

Sub SelectDuplicates()
    On Error Resume Next
    Dim c As Range
    Dim r As Long
    Dim myRange As Range
  
        For Each c In Selection
            If Application.CountIf(Selection, c) > 1 Then
'                If r = 0 Then
                    Set myRange = c
                    r = r + 1
                Else
                    Set myRange = Union(myRange, c)
                End If
            
        Next c
        myRange.Select
   MsgBox r & " duplicates found", vbInformation, "Duplicate search"
   On Error GoTo 0
End Sub

Select allOpen in new window

Thanks i have excel 2011 but with the remove duplicates it will delete the cells and would be an issue so need to delete the whole rows and just 1 which is a duplicate

Will the macro do this?

The macro will remove all duplicates by removing the entire roe leaving only unique items. Try it on a dummy file

Macro ran for few hrs but did not delete the duplicate rows
Column "A" has this

Anandan Sivamani
Anandan Sivamani

So one complete row should be deleted and one left alone

Can you attach the workbook with the code in and I'll check it

I would think that you could actually use AdvancedFilter to filter for Unique values as well

Data Tab -> AdvancedFilter. Choose Filter in Place and Unique Items

Roy - Adv Filter will indeed Filter in place for unique items but that would just hide the duplicates rather than remove them.

Thanks
Rob H

I actually should have said Filter to a different location

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.