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 with Scripting Dictionary
Remove Duplicates with VBA Collection
Demonstration

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 macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

I'll take a free VBA Developer Kit

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.

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 example 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"

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.