Optimized Methods and Performance Analysis for Extracting Unique Column Values in VBA

Problem Background and Performance Challenges

In Excel VBA development, extracting unique column values is a common data processing requirement. Traditional methods based on UsedRange loops and Scripting.Dictionary, while functionally complete, experience significant performance degradation when handling tens of thousands of records. This is primarily due to performance overhead from frequent interactions between VBA and the Excel object model.

Array Loading Optimization Method

By loading range data into an array in a single operation, the number of interactions with Excel can be dramatically reduced. Here is the core optimized code implementation:

Dim data(), dict As Object, r As Long
Set dict = CreateObject("Scripting.Dictionary")

data = ActiveSheet.UsedRange.Columns(1).Value

For r = 1 To UBound(data)
    dict(data(r, 1)) = Empty
Next

data = WorksheetFunction.Transpose(dict.keys())

The key advantages of this approach include: array operations occur in memory, avoiding repeated cell access; dictionary key assignment is more efficient than the Add method; and finally, the Transpose function converts the results back to array format.

Early Binding Performance Optimization

Using early binding can further enhance performance, requiring the addition of Microsoft Scripting Runtime via Tools-References:

Dim dict As New Scripting.Dictionary

Early binding determines object types at compile time, avoiding runtime type checking overhead, with particularly noticeable performance improvements in loop operations.

Performance Comparison Analysis

Through practical testing, the array-dictionary method proves 3-5 times faster than traditional approaches and 2-3 times faster than the AdvancedFilter method when processing 10,000 rows of data. Performance advantages become more pronounced as data volume increases.

Multi-dimensional Array Deduplication Extension

For complex scenarios requiring deduplication based on multiple columns, the dictionary method can be extended:

Public Sub RemoveDuplicates(data, ParamArray columns())
    Dim ret(), indexes(), ids(), r As Long, c As Long
    Dim dict As New Scripting.Dictionary

    If VarType(data) And vbArray Then Else Err.Raise 5, , "Argument data is not an array"

    ReDim ids(LBound(columns) To UBound(columns))

    For r = LBound(data) To UBound(data)
        For c = LBound(columns) To UBound(columns)
            ids(c) = data(r, columns(c))
        Next
        dict(Join$(ids, ChrW(-1))) = r
    Next

    indexes = dict.Items()
    ReDim ret(LBound(data) To LBound(data) + dict.Count - 1, LBound(data, 2) To UBound(data, 2))

    For c = LBound(ret, 2) To UBound(ret, 2)
        For r = LBound(ret) To UBound(ret)
            ret(r, c) = data(indexes(r - 1), c)
        Next
    Next

    data = ret
End Sub

This method uses a separator to concatenate multiple column values as dictionary keys, implementing deduplication functionality based on any combination of columns.

Practical Application Recommendations

In actual development, it is recommended to select the appropriate solution based on data scale: use AdvancedFilter for small datasets, and prioritize the array-dictionary method for large datasets. Additionally, pay attention to memory management by promptly releasing large array and dictionary objects.