In this VBA tutorial, I’ll show you two ways to remove duplicates from an array. The first method uses the scripting dictionary and the second uses collections. Each approach has some advantages and disadvantages, but both are great at returning the unique elements in your array.
Get these macros delivered right to your spreadsheet for free
Access tutorials and import my macros without ever leaving ExcelShow me - It's free
Remove Duplicates from Array with VBA Scripting Dictionary
Function RemoveDupesDict(MyArray As Variant) As Variant 'DESCRIPTION: Removes duplicates from your array using the dictionary method. 'NOTES: (1.a) You must add a reference to the Microsoft Scripting Runtime library via ' the Tools > References menu. ' (1.b) This is necessary because I use Early Binding in this function. ' Early Binding greatly enhances the speed of the function. ' (2) The scripting dictionary will not work on the Mac OS. 'SOURCE: https://wellsr.com '----------------------------------------------------------------------- Dim i As Long Dim d As Scripting.Dictionary Set d = New Scripting.Dictionary With d For i = LBound(MyArray) To UBound(MyArray) If IsMissing(MyArray(i)) = False Then .item(MyArray(i)) = 1 End If Next RemoveDupesDict = .Keys End With End Function
Pass this function a 1-dimensional array and it returns an array containing only the unique elements in your original array. In other words, it removes all duplicate entries from your array.
- This function will not work on Mac operating systems.
- It slows down considerably as your array size grows over a couple hundred thousand entries.
- You must add a reference to the Microsoft Scripting Runtime Library.
To reference the Microsoft Scripting Runtime Library, go to your Visual Basic Editor and click Tools > References. Scroll down until you see “Microsoft Scripting Runtime” and check the box.
The only reason this is necessary is because I used early-binding in the function. If I were to use late-binding, you wouldn’t need to add this reference, but the function would perform remarkably slower. Early-binding in this function produces an incredible performance boost.
Remove Duplicates from Array with VBA Collection
Function RemoveDupesColl(MyArray As Variant) As Variant 'DESCRIPTION: Removes duplicates from your array using the collection method. 'NOTES: (1) This function returns unique elements in your array, but ' it converts your array elements to strings. 'SOURCE: https://wellsr.com '----------------------------------------------------------------------- Dim i As Long Dim arrColl As New Collection Dim arrDummy() As Variant Dim arrDummy1() As Variant Dim item As Variant ReDim arrDummy1(LBound(MyArray) To UBound(MyArray)) For i = LBound(MyArray) To UBound(MyArray) 'convert to string arrDummy1(i) = CStr(MyArray(i)) Next i On Error Resume Next For Each item In arrDummy1 arrColl.Add item, item Next item Err.Clear ReDim arrDummy(LBound(MyArray) To arrColl.Count - LBound(MyArray) - 1) i = LBound(MyArray) For Each item In arrColl arrDummy(i) = item i = i + 1 Next item RemoveDupesColl = arrDummy End Function
Where the Scripting Dictionary method is great with smaller arrays, the Collection method excels for very large arrays. The
Just like the Dictionary method, the Collection method of returning unique elements also has limitations:
- It converts your array to an array of strings. For example, if you pass it an array of integers, it will return the unique elements as an array of strings.
That sounds like a pretty big limitation, but I rarely find it to be an issue. For you Mac users out there, you’ll appreciate that this method works on your operating system, too.
Using the Two Functions
These functions are quite simple to use. All you have to do is pass them a 1-dimensional array and they’ll return another array with all the duplicate items removed. Let’s take a look at an example calling the
Sub RemoveDuplicatesDemo_Dictionary() Dim arr1() As Variant Dim arr2() As Variant arr1 = Array("Cow", "Cat", "Cow", "Frog", "Pig", "Cat") arr2 = RemoveDupesDict(arr1) 'Dictionary Method End Sub
Now let’s take a look at an exmaple using the
Sub RemoveDuplicatesDemo_Collection() Dim arr1() As Variant Dim arr2() As Variant arr1 = Array("Cow", "Cat", "Cow", "Frog", "Pig", "Cat") arr2 = RemoveDupesColl(arr1) 'Collection Method End Sub
In both examples,
"Cow" "Cat" "Frog" "Pig"
If we change the array
arr1 = Array(1, 3, 5, 3, 5, 7)
and we run the same macros, the Dictionary method would return an array of unique integers:
1 3 5 7
However, the Collection method would return an array of strings:
"1" "3" "5" "7"
Fortunately, you don’t have to memorize these functions. By downloading the wellsrPRO Excel Add-in, you’ll be able to automatically import these functions into your Excel Workbook whenever you need them - without ever leaving Excel!
I know we covered quite a bit of material in this tutorial. If you feel overwhelmed, reach out to my VBA Consulting page and I’ll be happy to help you out!
If you’re ready to free up your time in the office, visit my Excel Add-ins page for some great automation products. There are plenty more grab-and-go macro examples in my VBA Code Library so take a look there, too!