vba 收集一列中的所有名称并将它们放入 Excel 中的数组中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6775165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Collect all names in a column and put them in an Array in Excel
提问by Jonas
I have an excel worksheet with a table of data. One column of the table contains the names of companies. For example, "Apple", "Microsoft", "Asus". The column may contain duplicate company names.
我有一个带有数据表的 Excel 工作表。表的一列包含公司名称。例如，"Apple"、"Microsoft"、"Asus"。该列可能包含重复的公司名称。
How can I populate an array in VBA which contains the distinctmembers of this column?
如何在 VBA 中填充包含此列不同成员的数组？
回答by JMax
You can use a vba collectionwhich does not allow duplicates for the same key:
您可以使用collection不允许相同键重复的 vba ：
Option Explicit
Sub UniqueList()
Dim i As Long
Dim rList As Range
Dim cUnique As New Collection
Dim aFinal() As String
'change the range depending on the size of your title (or use a named range)
Set rList = Range("A1:M1")
'Loop over every column and add the value to the collection (with unique key)
For i = 1 To rList.Columns.Count
On Error Resume Next
cUnique.Add rList(1, i), CStr(rList(1, i))
Next i
'Store back the value from the collection to an array
ReDim aFinal(1 To cUnique.Count, 1 To 1)
For i = 1 To cUnique.Count
aFinal(i, 1) = cUnique(i)
Next i
'Use aFinal to do whatever you want
End Sub
