3

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?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
TechGeek
  • 2,172
  • 15
  • 42
  • 69
  • 2
    As far as I remember `.RemoveDuplicates` doesn't work in Excel 2011. You will have to loop through the range to find duplicates. – Siddharth Rout Oct 30 '14 at 14:31
  • 1
    In line with @SiddharthRout's suggestion, use a dictionary or collection approach. It's more complicated but it will get the job done well. – WGS Oct 30 '14 at 15:49
  • @SiddharthRout & Nanashi: ok thanks. I will write a custom code then :( – TechGeek Oct 30 '14 at 17:29
  • Definitely not supported by Excel Mac VBA. When you record a macro and remove duplicates using the button on the ribbon (Data -> Remove Duplicates), the macro code method is empty :( – joehanna Jun 27 '15 at 05:34

2 Answers2

0

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
JPR
  • 572
  • 5
  • 12
user3173439
  • 1
-1

I think the answers to this are dated. I'm updating, in case someone else searches.
.removeduplicates works in Excel in mac. It should just be whatever your selection is and then .removeduplicates.

so this... Range().RemoveDuplicates

meimei
  • 9
  • 1