VBA – Remove Duplicates From Array

Associated Files Download Links

This tutorial will teach you how to remove duplicates from an Array in VBA.

Remove Duplicates

The easiest way to remove duplicates from a VBA Array is to assign the array values to a VBA Collection and then pass the values back to an Array. Collections don’t allow duplicate values and thus using a Collection, we can remove duplicates from an array.  We’ve created a function to perform this task:

Function ArrayRemoveDups(MyArray As Variant) As Variant
    Dim nFirst As Long, nLast As Long, i As Long
    Dim item As String
    
    Dim arrTemp() As String
    Dim Coll As New Collection

    'Get First and Last Array Positions
    nFirst = LBound(MyArray)
    nLast = UBound(MyArray)
    ReDim arrTemp(nFirst To nLast)

    'Convert Array to String
    For i = nFirst To nLast
        arrTemp(i) = CStr(MyArray(i))
    Next i
    
    'Populate Temporary Collection
    On Error Resume Next
    For i = nFirst To nLast
        Coll.Add arrTemp(i), arrTemp(i)
    Next i
    Err.Clear
    On Error GoTo 0

    'Resize Array
    nLast = Coll.Count + nFirst - 1
    ReDim arrTemp(nFirst To nLast)
    
    'Populate Array
    For i = nFirst To nLast
        arrTemp(i) = Coll(i - nFirst + 1)
    Next i
    
    'Output Array
    ArrayRemoveDups = arrTemp

End Function


Sub ArrTest()
    Dim strNames(1 To 4) As String
    Dim outputArray() As String
    Dim i As Long
    Dim item As Variant

    'Set Initial Array Values
    strNames(1) = "Shelly"
    strNames(2) = "Steve"
    strNames(3) = "Neema"
    strNames(4) = "Steve"
    
    'Call Dup Function
    outputArray = ArrayRemoveDups(strNames)
    
    'Output values to Immediate Window (CTRL + G)
    For Each item In outputArray
        Debug.Print item
    Next item

End Sub

Note: In the example we forced our Array to start at 1 (not 0). If your array starts at 0 you will need to modify the code slightly.

Notice that we convert the array contents to a string. If necessary, you could convert the strings back to integers after the process is complete.