Clear duplicate content in xl column

Occasional Visitor

I am trying to clear duplicate content in the cells in column B of my xl table.


Remove Duplicates function is not suitable because it deletes rows.


I only need to clear the duplicate content in the cells from column B.

5 Replies


Select column B only and then click Remove Duplicates on the Data tab of the ribbon.

Excel may display a warning:


Select 'Continue with the current selection', then click Remove Duplicates...

Excel will leave the other columns alone.


My previous reply won't work if your data are in a table, since the data in a table row belong together. Deleting duplicates in a single column of a table makes no sense.

If you really need to do that, your data shouldn't have been in a table in the first place, so convert the table to a range. Then follow the steps from my previous reply.

What do you mean by clear? Are you wishing to hide duplicate items? If so then use conditional formatting.


If you want to clear duplicate cells in column B:

Sub ClearDups()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("B" & Rows.Count).End(xlUp).Row
    For r = 3 To m
        If Application.CountIf(Range("B2:B" & r - 1), Range("B" & r).Value) Then
            Range("B" & r).ClearContents
        End If
    Next r
    Application.ScreenUpdating = True
End Sub


An alternative could be Power Query.

clear values.JPG