excel - VBA，将过滤后的值存储在数组中

Question

我的代码过滤掉了空白和 0 条记录，但我的数组正在获取所有值。
我怎样才能考虑过滤的记录？这是我能做到的最好方法吗？

Sub FilterAndCopy()
Dim LastRow As Long
Dim Arr As Variant

With Worksheets("BusinessDetails")
    .Range("$A5:$AJ5").AutoFilter field:=33, Criteria1:="<>", Criteria2:="<>0", Criteria2:="<>-0"
    LastRow = .Range("AG" & .Rows.Count).End(xlUp).Row

    Arr = Range("AG8:AG" & LastRow)
    Dim R As Long
    Dim C As Long
    For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
        For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
            Debug.Print Arr(R, C)
        Next C
    Next R

    Dim Destination As Range
    Set Destination = Sheets(2).Range("D10")
    Set Destination = Destination.Resize(UBound(Arr), 1)
    Destination.Value = Application.Transpose(Arr)

     Sheets(1).ShowAllData

End With
End Sub

更新代码:

Sub FilterAndCopy()
Dim LastRow As Long
Dim Arr As Variant

With Worksheets("BusinessDetails")
    .Range("$A5:$AJ5").AutoFilter field:=33, Criteria1:="<>", Criteria2:="<>0", Criteria2:="<>-0"
    LastRow = .Range("AG" & .Rows.Count).End(xlUp).Row

    Set rFiltered = Range("A5:AJ" & LastRow).SpecialCells(xlCellTypeVisible)

    ReDim Arr(1 To rFiltered.Areas.Count)
    I = 0
    For Each V In rFiltered.Areas
        I = I + 1
        Arr(I) = V
    Next V


     rFiltered.Copy Sheets("Step 4").Range("D10")


End With
End Sub

Answer 1

过滤范围时，会留下不同的 Areas .

因此，您的选择是一次将一个单元格读入数组，或者一次将一个区域作为数组读入 Parent。大批。

例如，(数据在 A1:C9 中，过滤在 A 列完成)

With Worksheets("Sheet1")
    .Range("$A1:$C9").AutoFilter field:=1, Criteria1:="<>", Criteria2:="<>0"
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row


Set rFiltered = Range("A1:C" & LastRow).SpecialCells(xlCellTypeVisible)
ReDim Arr(1 To rFiltered.Areas.Count)

I = 0
For Each V In rFiltered.Areas
    I = I + 1
    Arr(I) = V
Next V

Arr 现在将是一个数组数组，仅包含过滤后的单元格。

备注

如果您只想复制过滤后的范围，那么:

rFiltered.Copy Sheets("sheet2").Range("D10")

注2

如果您总是要复制，则可以将该数据放入数组中，例如(未测试):

arr = Sheets("sheet2").Range("D10").CurrentRegion