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.