In this VBA tutorial, I’ll show you two ways to remove duplicates from an array. The first method uses the VBA 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.
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
Make powerful spreadsheets with our free VBA Developer's Guide Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
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.
It’s great to be able to remove duplicates from your arrays, but arrays can do so much more than that. To become really good at using arrays, you’ll need to grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.
- 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 example 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"
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.