arrays - Excel宏将整列转换为一维数组

Question

我有一个这样的数据表

ID    Name
-------------------
B23     Max
D27     Nads
W34     sads
A65     Robin
C37     Harvard
C65     Nivkai
V87     adsdasd
Q78     sadsad

我需要将所有 ID 作为字符串放入“一维数组”中。所以我尝试了这个，

Dim RowCount As Integer
RowNumber = wb1.Sheets(1).UsedRange.Rows.Count

Dim idArray() As String
For j = 1 To RowNumber
    ID = wb1.Sheets(1).Cells(j, 1).Value
    ReDim idArray(j)
    idArray(j) = CStr(ID)
Next j

我的主要目标是以这种方式使用此 idArray 在不同的工作表上应用过滤器

wb2.Sheets(1).Range(Selection, Selection.End(xlDown)).AutoFilter Field:=1, Criteria1:=idArray(), Operator:=xlFilterValues

但后来当我尝试使用下面的代码打印整个数组时，它什么也没打印。而且似乎 idArray() 是空的。

For n = 1 To UBound(idArray)
    Debug.Print QidArray(n)
Next n

谁能告诉我我做错了什么。

谢谢

Answer 1

虽然 Mehow 已经 Eloquent 地回答了您的具体要求，但更一般地说，请尽量避免循环遍历工作表 - 它很慢。将数组复制到内存中并循环遍历它总是更快 - 这里有一些替代方案:

<强>1。仅独特值(value)

Sub UniqueValuesOnly()

Dim vData, idArray  As Variant
Dim x               As Long
Dim oDic            As Object

Set oDic = CreateObject("scripting.dictionary")

vData = Sheets(1).Cells(1, 1).CurrentRegion.Resize(, 1).Value

For x = LBound(vData) + 1 To UBound(vData)
    If Not oDic.exists(vData(x, 1)) Then
        oDic.Add vData(x, 1), Nothing
    End If
Next x

idArray = oDic.keys


End Sub

<强>2。少量数据不循环

Sub AllSmallData()

Dim idArray

With Sheets(1).Cells(1, 1).CurrentRegion
    idArray = Application.Transpose(.Offset(1).Resize(.Rows.Count - 1, 1).Value)
End With

End Sub

<强>3。循环数组获取大量数据

Sub AllLargeData()
Dim idArray() As String, vData
Dim x As Long

With Sheets(1).Cells(1, 1).CurrentRegion
    vData = .Offset(1).Resize(.Rows.Count - 1, 1).Value
End With

ReDim idArray(1 To UBound(vData))

For x = LBound(vData) To UBound(vData)
    idArray(x) = vData(x, 1)
Next x

End Sub