Hello jk@CU,
Here is version similar to yours. The Dictionary object is an Associative Array. This makes it faster and easier to reference the elements because of the Exists method. This will tell you if an element is already in the array. The additional advantage over a regular array is the index or "key" can be any of any data type as can the contents or "item" except a User Define Type.
Sub DeleteUniques()
Dim Cell As Range
Dim DSO As Object
Dim Item As Variant
Dim Items As Variant
Dim Key As Variant
Dim Rng As Range
Set Rng = Selection.Resize(ColumnSize:=1)
Set DSO = CreateObject("Scripting.Dictionary")
DSO.CompareMode = vbTextCompare
For Each Cell In Rng
Key = Trim(Cell.Value)
If Key <> "" Then
If Not DSO.Exists(Key) Then
DSO.Add Key, Cell
Else
DSO(Key) = ""
End If
End If
Next Cell
Items = DSO.Items
Set Rng = Nothing
For Each Item In Items
If Item <> "" Then
If Rng Is Nothing Then Set Rng = Item
Set Rng = Union(Rng, Item)
End If
Next Item
If Not Rng Is Nothing Then Rng.EntireRow.Delete
Set DSO = Nothing
End Sub
Bookmarks