Excel VBA Array Duplicate Values: Find, Keep, or Remove

VBA & macros · Updated November 21, 2026

Yes. To find duplicate values in an Excel VBA array, scan its elements and track values already seen; to remove duplicates while keeping the first occurrence, a dictionary is a compact option. The example below returns a new array in the original order, so it does not change the source array.

What counts as a duplicate in a VBA array?

An array can contain repeated values just as a worksheet range can. VBA does not automatically prevent duplicates or provide a built-in array function that removes them. You decide what counts as equal, then compare each element with the values you have already encountered.

That decision matters because two values can look alike but have different underlying types. For example, the number 1 and the text value “1” may be treated as the same if both are converted to text before comparison. Likewise, “Pear” and “pear” are equal in a case-insensitive comparison but different in a case-sensitive one. Empty values and empty strings may also become indistinguishable after conversion.

The code here uses text comparison without regard to case. It therefore keeps the first value encountered and skips later values with the same text representation, including values that differ only by capitalization. This is convenient for many lists of names or labels, but it is not a universal definition of equality.

Remove duplicates while preserving the original order

This version uses the Windows Scripting.Dictionary object, created at runtime so you do not have to add a reference in the VBA editor. It returns a separate, compact array; the input remains unchanged. The example expects a non-empty, one-dimensional array of ordinary values such as text, numbers, or dates. It does not handle Null, Excel error values, objects, or nested arrays.

Option Explicit

' Return first occurrences, comparing text without case sensitivity.
Function UniqueValuesInOrder(ByVal values As Variant) As Variant
    Dim seen As Object
    Dim result() As Variant
    Dim i As Long
    Dim used As Long
    Dim key As String

    Set seen = CreateObject("Scripting.Dictionary")
    seen.CompareMode = vbTextCompare

    ReDim result(LBound(values) To UBound(values))

    For i = LBound(values) To UBound(values)
        key = CStr(values(i))
        If Not seen.Exists(key) Then
            seen.Add key, True
            result(LBound(values) + used) = values(i)
            used = used + 1
        End If
    Next i

    ReDim Preserve result(LBound(values) To LBound(values) + used - 1)
    UniqueValuesInOrder = result
End Function

Sub ShowUniqueValues()
    Dim source As Variant
    Dim uniqueValues As Variant
    Dim i As Long

    source = Array("Red", "blue", "Red", "BLUE", "Green")
    uniqueValues = UniqueValuesInOrder(source)

    For i = LBound(uniqueValues) To UBound(uniqueValues)
        Debug.Print CStr(uniqueValues(i))
    Next i
End Sub

In this sample, the Immediate window displays Red, blue, and Green, in that order. The later Red and BLUE entries are omitted because the comparison ignores capitalization. To inspect the Immediate window in the VBA editor, press Ctrl+G on Windows; on Mac, use the menu command View > Immediate Window if that command is available in your editor.

To run the macro in desktop Excel, press Alt+F11 on Windows to open the Visual Basic Editor, insert a standard module, and paste the code. On Mac, open the Visual Basic Editor from the Developer tab or Tools > Macro > Visual Basic Editor; keyboard shortcuts can vary by keyboard and Excel settings. Save the workbook as a macro-enabled .xlsm file. Save a copy before running any macro: VBA changes cannot be undone with Ctrl+Z.

How the duplicate check works

The dictionary acts as a record of keys already encountered. For every element, the loop converts its value to a string, checks whether that key is present, and adds the original value to the output only if the key is new. The separate output array preserves the input order because elements are considered from the lower bound to the upper bound.

  • LBound and UBound identify the first and last valid indexes. VBA arrays do not always start at zero, so using these bounds is safer than assuming the first element is element 0.
  • CompareMode sets the dictionary’s text comparison behavior. vbTextCompare ignores letter case; vbBinaryCompare distinguishes uppercase and lowercase text.
  • used tracks how many unique values have been copied. The final ReDim Preserve shrinks the result to that count.

The function uses CStr to create dictionary keys, but it copies the original value into the result. As a result, the first value’s original type is retained in the output. However, key conversion means a numeric 1 and a text “1” collide, as can Empty and an empty string. If those distinctions are important, use a key scheme that includes the value’s type, or compare values with logic tailored to the data.

Choose case-sensitive or case-insensitive matching

For identifiers where capitalization is meaningful, change seen.CompareMode = vbTextCompare to seen.CompareMode = vbBinaryCompare. With binary comparison, Red and red are distinct keys. Set CompareMode before adding any keys; changing it after dictionary entries have been added can cause an error.

There is a subtle limit to that change: the code first applies CStr to each item. Binary comparison then distinguishes the resulting strings, but it still does not distinguish different values that convert to exactly the same string. If the array mixes types and type identity matters, include VarType in the key. For example, a key can combine the type number, a separator, and the converted value. Choose a separator or encoding that cannot create ambiguous keys for your data.

For numeric data, decide whether values such as 2 and 2.0 should count as equal, and whether rounding should be applied before comparison. For dates, decide whether two values representing the same date but stored with different time components are duplicates. These are data rules, not decisions VBA can infer from the word duplicate.

Use a portable loop when the dictionary is unavailable

Scripting.Dictionary is a Windows COM object and may not be available in Excel for Mac. A nested-loop comparison avoids that dependency and works for ordinary values on either desktop platform. It is simpler to deploy but slower for large arrays, because each new item may be compared with many previously kept items.

Option Explicit

' Portable alternative for a non-empty, one-dimensional array.
Function UniqueValuesPortable(ByVal values As Variant) As Variant
    Dim result() As Variant
    Dim i As Long
    Dim j As Long
    Dim used As Long
    Dim found As Boolean

    ReDim result(LBound(values) To UBound(values))

    For i = LBound(values) To UBound(values)
        found = False
        For j = LBound(values) To LBound(values) + used - 1
            If StrComp(CStr(values(i)), CStr(result(j)), vbTextCompare) = 0 Then
                found = True
                Exit For
            End If
        Next j

        If Not found Then
            result(LBound(values) + used) = values(i)
            used = used + 1
        End If
    Next i

    ReDim Preserve result(LBound(values) To LBound(values) + used - 1)
    UniqueValuesPortable = result
End Function

This portable version uses the same case-insensitive, text-based comparison rules as the dictionary example. It also has the same assumptions about non-empty one-dimensional arrays and values that can be converted with CStr. Its advantage is fewer platform dependencies; its disadvantage is performance as the list grows. For a few dozen items, the difference is usually unimportant. For a very large array, prefer an available dictionary or another suitable data structure.

Find duplicates without removing them

Sometimes the goal is to identify repeated values rather than build a unique list. A dictionary can count occurrences instead of merely recording whether a key exists: add one to the stored count each time a key appears, then inspect the counts. A value is a duplicate when its count is greater than 1. If you need to report each repeated value only once, keep a second record of which duplicate keys have already been reported.

For a quick check on a small array, a nested loop can compare each element with later elements and flag a match. Be careful about the intended result: that approach may encounter the same repeated value more than once. For example, in A, A, A there are three matching pairs, but only one distinct duplicated value. Counting repeated entries and counting duplicate pairs are different tasks.

If the array comes from a worksheet range, it is often useful to write the resulting values or counts to a separate range rather than changing cells during the scan. A multi-cell range assigned to a Variant is generally represented as a two-dimensional array, even when the range has one column. The one-dimensional functions above therefore cannot be applied directly to that range array without first selecting a column or adapting the loops to use both row and column bounds.

Common errors and practical safeguards

  • Empty arrays: The examples assume at least one element. Calling LBound or UBound on an uninitialized dynamic array can raise an error. Check that the array has been initialized, or add explicit error handling for the empty-array case.
  • Two-dimensional range data: A range such as A1:A10 still produces a two-dimensional Variant array. Indexing it as values(i) is not equivalent to indexing values(i, 1).
  • Null and error values: CStr cannot safely convert every possible Variant subtype. Handle Null and CVErr values before building keys if they may occur.
  • Dictionary creation: CreateObject can fail if the required Windows component is unavailable or restricted. Use the portable nested loop or a suitable alternative in that situation.
  • Changed data: The returned array is a snapshot. If you edit the source after calling the function, call it again to calculate a fresh result.

When writing results back to a worksheet, remember that a VBA array and a worksheet range have different shapes and bounds. Resize the destination range to match the number of returned values, and handle the one-result case deliberately. A mismatch between the output array dimensions and the target range dimensions is a common source of assignment errors.

VBA arrays compared with worksheet tools

Use VBA when the values are already in memory, the process is part of a macro, or you need to apply custom comparison rules. For a list held in cells, Excel’s Remove Duplicates command may be quicker: select the data, then use Data > Remove Duplicates. It changes the selected data, so copy the range first if you need to preserve the original list.

In Microsoft 365 for Windows, the UNIQUE worksheet function can return distinct values from a range or array and is often a simpler choice when a formula-based result is appropriate. For example, with values in A2:A20, =UNIQUE(A2:A20) spills a list of distinct values. The function is also available in Excel 2021 and Excel for the web. Formula results update with the source data, while a VBA-created array only changes when the macro runs again. The best method depends on whether you want a live worksheet result, a one-time edit, or custom code.

Frequently Asked Questions

Does VBA have a built-in function to remove duplicates from an array?
No. VBA does not include a general-purpose array deduplication function. You can write a loop, use a dictionary where supported, or use a worksheet feature or function when the values are in cells.

How do I remove duplicates but keep the first occurrence?
Scan the array from its lower bound to its upper bound, add unseen values to a new array, and skip keys already recorded. The examples above preserve the first occurrence and retain the order of the remaining values.

Can I remove duplicates from an array without changing its order?
Yes. The examples append each first-seen value to the result as the input is scanned. They do not sort the output, so its order follows the original array.

Why does my range array give a subscript error?
A range with multiple cells is generally loaded into a two-dimensional Variant array. Code that expects a one-dimensional array may use the wrong number of indexes. Loop through both dimensions or copy the relevant column into a one-dimensional array first.

Can I run VBA macros in Excel for the web?
No. Excel for the web can open a workbook containing macros, but it cannot create, run, or edit VBA macros. Use desktop Excel for VBA; a worksheet formula such as UNIQUE may be an option when you need a result in the browser.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.