VBA:如何在列中获取唯一值并将其插入数组? [英] VBA: How do I get unique values in a column and insert it into an array?

问题描述

我已经看到有关此主题的多个代码，但是我似乎看不懂.

I have seen multiple codes regarding this topic but I can't seem to understand it.

例如，如果我有一栏记录人的名字，我想将所有唯一的名字记录到数组中.

For instance, if I have a column that records people names, I want to record all unique names into the array.

所以如果我有一列姓名

David
Johnathan
Peter
Peter
Peter
Louis
David

我想利用VBA从列中提取唯一名称并将其放入数组中，这样当我调用数组时它将返回这些结果

I want to utilize VBA to extract unique names out of the column and place it into an array so when I call the array it would return these results

Array[0] = David
Array[1] = Johnathan
Array[2] = Peter
Array[3] = Louis

推荐答案

尽管提到了 Collection 并且是可能的解决方案，但是使用 Dictionary 效率更高>，因为它具有 Exists 方法.然后，只需将名称添加到字典(如果尚不存在)，然后在完成后将键提取到数组即可.

Despite a Collection being mentioned and being a possible solution, it is far more efficient to use a Dictionary as it has an Exists method. Then it's just a matter of adding the names to the dictionary if they don't already exist, and then extracting the keys to an array when you're done.

请注意，我已经使名称比较区分大小写，但是您可以根据需要将其更改为不区分大小写.

Note that I've made the name comparisons case-sensitive, but you can change that if necessary, to case-insensitive.

Option Explicit

Sub test()

   'Extract all of the names into an array
    Dim values As Variant
    values = Sheet1.Range("Names").Value2 'Value2 is faster than Value

    'Add a reference to Microsoft Scripting Runtime
    Dim dic As Scripting.Dictionary
    Set dic = New Scripting.Dictionary

    'Set the comparison mode to case-sensitive
    dic.CompareMode = BinaryCompare

    Dim valCounter As Long
    For valCounter = LBound(values) To UBound(values)
        'Check if the name is already in the dictionary
        If Not dic.Exists(values(valCounter, 1)) Then
            'Add the new name as a key, along with a dummy value of 0
            dic.Add values(valCounter, 1), 0
        End If
    Next valCounter

    'Extract the dictionary's keys as a 1D array
    Dim result As Variant
    result = dic.Keys

End Sub

这篇关于VBA:如何在列中获取唯一值并将其插入数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！