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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
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.
