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.