Function to find Unique Items in a list

It happens many times when you are writing a Macro and you need to find the unique items out of the a range of items. Suppose you have an excel sheet that has the employee ids of you the people working in a company but the list has duplicate entries. Though there is a feature in excel where you can just display the unique name but to do the same using Macro you need to write a code. This is required whenever you are dealing with duplicate items in a list but you are only interested in the unique values.The below code take range of items that has duplicate value and return the range of unique items. Use this code anywhere in your Macro.

Function UniqueItemList(InputRange As Range, _
HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula <> "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UniqueItemList = ""
If cUnique.Count > 0 Then
ReDim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
UniqueItemList = uList
If Not HorizontalList Then
UniqueItemList = _
Application.WorksheetFunction.Transpose(UniqueItemList)
End If
End If
On Error GoTo 0
End Function

Subscribe to my blog for more Excel Tips :)