Removing all values that appears more than once Leave ONLY one Unique

New Contributor

Hello Team,

How can I remove just a single cell duplicate without affecting whole rows for duplicate value? I have more than 160,000 rows, with a lot of those duplicates. Below is an example of what I need. I have also attached an excel file, I am using 365.





2 Replies
best response confirmed by TuM_29 (New Contributor)


Here is a macro that you can run:

Sub ClearDuplicates()
    Dim r As Long
    Dim m As Long
    Dim v As Variant
    Application.ScreenUpdating = False
    m = Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("A1:B" & m).Value
    For r = m To 3 Step -1
        If v(r, 1) = v(r - 1, 1) Then
            v(r, 1) = ""
            v(r, 2) = ""
        End If
    Next r
    Range("A1:B" & m).Value = v
    Application.ScreenUpdating = True
End Sub

@Hans Vogelaar 

Thank you so much, this is helpful ..... Now I need to be familiar with Macro for sure