Excel VBA中的过滤器数组

debugcn 发表于 Dev

本多瓦

如何过滤多维数组？

我的代码既不工作也不整洁：

Option Explicit
Sub CalcE()
Dim TotalRows As Long
Dim myArray, myArray2 As Variant
Dim i, a As Integer

填充数组

TotalRows = Sheets("Data").Rows(Rows.Count).End(xlUp).Row
myArray = Sheets("Data").Range("A5:F" & TotalRows)
MsgBox "Array populated with " & UBound(myArray) & "entries."

将 myArray 条目过滤到 myArray2 中，但仅过滤第 1、4 和 6 列。

a = 0
For i = 0 To UBound(myArray)
    If myArray(i, 1) > 1 Then
        myArray2(a, 1) = myArray(i, 1)
        myArray2(a, 2) = myArray(i, 4)
        myArray2(a, 3) = myArray(i, 6)
        a = a + 1
    End If
Next i    
MsgBox "Array populated now with " & UBound(myArray2) & "entries."
End Sub

我用谷歌搜索并发现 Excel 中的数组似乎是非常不灵活的东西，不适合使用。人们通常会放弃 VBA 数组，转而使用 AutoFilter 方法。我担心是否真的没有什么好办法。拥有它真是太好了！

Subodh Tiwari sktneer

如果您只需要将第 1、4 和 6 列存储到中myArray2，请尝试一下...

Dim ws As Worksheet
Set ws = Sheets("Data")
TotalRows = ws.Rows(Rows.Count).End(xlUp).Row
myArray2 = Application.Index(ws.Cells, Evaluate("Row(5:" & TotalRows & ")"), Array(1, 4, 6))

或者你可以像这样调整你的代码......

Dim ws As Worksheet
Set ws = Sheets("Data")
Dim cnt As Long, j As Long
TotalRows = ws.Rows(Rows.Count).End(xlUp).Row
myArray = ws.Range("A5:F" & TotalRows).Value
cnt = Application.CountIf(ws.Range("A5:A" & TotalRows), ">1")
If cnt = 0 Then Exit Sub
ReDim myArray2(1 To cnt, 1 To 3)
For i = 1 To UBound(myArray, 1)
    If myArray(i, 1) > 1 Then
        j = j + 1
        myArray2(j, 1) = myArray(i, 1)
        myArray2(j, 2) = myArray(i, 4)
        myArray2(j, 3) = myArray(i, 6)
    End If
Next i

MsgBox UBound(myArray2, 1)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。