Remove duplicates on a Mac (Last occurrence is kept)

Removing duplicates on a Mac is slightly different to on a PC version of Excel. This is more brute force than elegant and may take a little longer, but it does work.


'Code designed 25/2/2017 by datanology.co.uk

Application.ScreenUpdating = False

'Find last row
With ActiveSheet
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

For d = lastrow To 2 Step -1

'Keep an eye on how it's progressing
Application.StatusBar = d

vl = ActiveSheet.Range("B" & d) & ActiveSheet.Range("F" & d) & ActiveSheet.Range("G" & d)

For A = d - 1 To 2 Step -1

vl2 = ActiveSheet.Range("B" & A) & ActiveSheet.Range("F" & A) & ActiveSheet.Range("G" & A)

If vl = vl2 And ActiveSheet.Range("B" & A) <> "" Then
ActiveSheet.Range("A" & A).Interior.Color = 255
Exit For
End If

Next A

Next d

'Remove the red highlighted rows
For dl = lastrow To 2 Step -1

'Keep an eye on how it's progressing
Application.StatusBar = dl

If ActiveSheet.Range("A" & dl).Interior.Color = 255 Then ActiveSheet.Range("A" & dl).EntireRow.Delete shift:=xlUp
Next dl

Application.StatusBar = False






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology