Excel VBA - RemoveDuplicates method does not work

问题:

I have below piece of code to remove duplicates from a sheet by looking into two columns (column 3 & 5).

lRow = .Cells(Rows.Count, "A").End(xlUp).Row
'.Range("A1:BR" & lRow).RemoveDuplicates Columns:=Array(3, 5), Header:=xlYes
.Range("$A$1:$BR$" & lRow).RemoveDuplicates Columns:=Array(3, 5), Header:=xlYes

It works fine in Windows but unfortunately not on Mac.

Can anybody please suggest me what do I need to change here?

回答1:

This piece of code will create a list of unique values and copy into another cell. So create unique list.

You have to specify where your list starts, and where you want to copy to. You can do this by changing the fromCell and toCell variables. I hope this helps.

Sub uniqueList()

    fromCell = "A1"
    toCell = "B1"

    fromColumn = Mid(fromCell, 1, 1) 'This will resolve to A
    toColumn = Mid(toCell, 1, 1)     'This will resolve to B

    fromRow = Mid(fromCell, 2)       'This will resolve to 1
    toRow = Mid(toCell, 2)           'This will resolve to 1


    Dim cl As Range, UniqueValues As New Collection, uValue As Variant
    Application.Volatile

    numRows = Range(fromCell).End(xlDown).Row

    On Error Resume Next
    For Each cl In Range(fromCell & ":" & fromColumn & numRows)
        UniqueValues.Add cl.Value, CStr(cl.Value)
    Next cl

    y = toRow - 1

    For Each uValue In UniqueValues
        y = y + 1
        Range(toColumn & y) = uValue
    Next uValue


End Sub