CODEWITHSUNDEEP
excelvba
SiSi
SiSi

Reputation: 121

RemoveDuplicates Method doesn't work well, it remains two or more of duplicates

Sub no_DuplicatesNsort()
    Sheets(1).Columns(1).RemoveDuplicates Columns:=1, Header:=xlYes
    Sheets(1).Columns(1).Sort Key1:=Cells(1, 1), Order1:=xlAscending
End Sub

Is there any problem in this code? The data that I want to remove duplicates is located sheets(1).Columns(1), and every time I do run the code, then it keeps remaining at least 2 duplicates for every value.

I want to have a clear data without any duplicates. What am I missing for the code?

Upvotes: 2

Views: 83

Answers (1)

VBasic2008
VBasic2008

Reputation: 54815

Application.Trim, Sort, and RemoveDuplicates

... applied on a single-column range

Sub Test()
    
    Dim rg As Range: Set rg = ThisWorkbook.Worksheets(1).UsedRange.Columns(1)
    
    With rg ' use a range variable to get the IntelliSense working
        .Value = Application.Trim(.Value)
        .Sort rg, xlAscending, , , , , , xlYes
        .RemoveDuplicates 1, xlYes ' works faster on a sorted range
    End With

End Sub

Upvotes: 2

Related Questions