Sub test()
Dim Values() As Variant
Values = GetUniqueVals(Selection)
Dim i As Integer
For i = LBound(Values) To UBound(Values)
Debug.Print (Values(i))
MsgBox (Values(i))
Next
End Sub
Function GetUniqueVals(ByRef Data As Range) As Variant()
Dim cell As Range
Dim uniqueValues() As Variant
ReDim uniqueValues(0)
For Each cell In Data
If Not IsEmpty(cell) Then
If Not InArray(uniqueValues, cell.Value) Then
If IsEmpty(uniqueValues(LBound(uniqueValues))) Then
uniqueValues(LBound(uniqueValues)) = cell.Value
Else
ReDim Preserve uniqueValues(UBound(uniqueValues) + 1)
uniqueValues(UBound(uniqueValues)) = cell.Value
End If
End If
End If
Next
GetUniqueVals = uniqueValues
End Function
Function InArray(ByRef SearchWithin() As Variant, ByVal SearchFor As Variant) As Boolean
Dim i As Integer
Dim matched As Boolean
For i = LBound(SearchWithin) To UBound(SearchWithin)
If SearchWithin(i) = SearchFor Then matched = True
Next
InArray = matched
End Function