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.
vbTextCompareignores letter case;vbBinaryComparedistinguishes 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
- Excel Solver Not Responding? Fixes and Causes
- Excel Solver: Whole Numbers, Integers, and Constraints
- Excel Solver Not Finding a Solution? Troubleshoot It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Loop Through a Named Range in VBA: Practical ExamplesTo loop through every cell in an Excel named range in VBA, assign the range to a Range…
- Excel VBA Function Return: Values, Syntax, and ExamplesAn Excel VBA function returns a value by assigning that value to the function’s own name; VBA does…
- Clear Excel VBA: Contents, Formats, and CellsTo clear cells with Excel VBA, use Range("B2:D20").ClearContents to remove values and formulas while keeping formatting, or Range("B2:D20").Clear…
- Excel VBA TextBox for Data Entry: Setup and CodeYes. In Excel for Microsoft 365 on Windows, a VBA TextBox can collect information through a UserForm or…
- Excel VBA Dictionary Length: Count Entries and KeysIn Excel VBA, use a Dictionary’s Count property to find how many key-item pairs it contains. If by…
- Excel VBA String Limit: Maximum Length and Cell CapsA variable-length VBA String can hold up to approximately 2 billion characters in theory, but available memory usually…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.