Remove Duplicates from a Range

How often have you had a list of items and needed to list only the unique values? The following function goes through a range and stores only the unique values. UniqueValues (OrigArray)

The arguments are

OrigArray—An array from which to remove duplicates.

This first section (Const declarations) must be at the top of the module:

Const ERR_BAD_PARAMETER = "Array parameter required" Const ERR_BAD_TYPE = "Invalid Type" Const ERR_BP_NUMBER = 20000 Const ERR_BT_NUMBER = 20001

The following section of code can be placed anywhere in the module as long as it is below the previous section:

Public Function UniqueValues(ByVal OrigArray As Variant) As Variant Dim vAns() As Variant Dim lStartPoint As Long Dim lEndPoint As Long Dim lCtr As Long, lCount As Long Dim iCtr As Integer Dim col As New Collection Dim sIndex As String Dim vTest As Variant, vltem As Variant Dim iBadVarTypes(4) As Integer

'Function does not work if array element is one of the

'following types iBadVarTypes(0) = vbObject iBadVarTypes(l) = vbError iBadVarTypes(2) = vbDataObject

0 0

Post a comment