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.

Remove Duplicates with Scripting Dictionary
Remove Duplicates with VBA Collection
Demonstration


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show 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.

The RemoveDupesDict function is extremely fast for arrays containing up to a couple hundred thousand elements, but it does have a few limitations:

  1. This function will not work on Mac operating systems.
  2. It slows down considerably as your array size grows over a couple hundred thousand entries.
  3. 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 RemoveDupesColl removes duplicate entries from your VBA arrays with reasonable speed for arrays as large as a couple million entries.

Just like the Dictionary method, the Collection method of returning unique elements also has limitations:

  1. 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 RemoveDupesDict function:

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 RemoveDupesColl function:

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, arr2 becomes an array of strings containing the following items:

"Cow"
"Cat"
"Frog"
"Pig"

If we change the array arr1 to an array of strings, like this:

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!

Please subscribe to my email list and share this article with your friends on Facebook, Twitter, and Google+.

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!

Check out more VBA Tutorials

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA products, including Mouse To Macro. See more Excel products, including online courses and books, by visiting the Excel VBA Store.

Excel VBA Store