VBA – Remove Duplicates From Array

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on June 30, 2022

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:

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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as

Learn More!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples