arrays - 创建行数组VBA

Question

VBA 新手。我正在尝试创建一个行数组。

基本上，我有一整张工作表，并且想要在第 8 列中获取以某个值(“MA”)开头的所有行。

我最终想要操纵该数组(好像它是一个范围)，并将其粘贴到工作表的其他位置。任何人都可以帮忙吗？到目前为止，这是我的代码:

Dim top0M As Variant
ReDim top0M(1 To 1) As Variant

For i = 4 To Rows.Count
    If Cells(i, 8).Value Like "MA*" Then
        top0M(UBound(top0M)) = Rows(i)
        ReDim Preserve top0M(1 To UBound(top0M) + 1) As Variant
    End If
Next i

这段代码运行，但我不知道如何调试它以知道我是否有正确的行。我可以像粘贴范围一样粘贴这些行吗？

Answer 1

这会设置范围并将整个加载到一个数组中，然后它会加载一个包含您想要的行的不同数组:

With ActiveSheet 'This should be changed to the name of the worksheet: Worksheets("MySheet")
    Dim rng As Range
    Set rng = .Range(.Cells(4, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(4, .Columns.Count).End(xlToLeft).Column))


    Dim tot As Variant
    tot = rng.Value

    Dim top0M As Variant
    ReDim top0M(1 To Application.CountIf(.Range("H:H"), "MA*"), 1 To UBound(tot, 2)) As Variant
    Dim k As Long
    k = 1
    Dim i As Long
    For i = LBound(tot, 1) To UBound(tot, 1)
        If tot(i, 8) Like "MA*" Then
            Dim j As Long
            For j = LBound(tot, 2) To UBound(tot, 2)
                top0M(k, j) = tot(i, j)
            Next j
            k = k + 1
        End If
    Next i
End With

'to print to a sheet just assign the values:

Worksheets("sheet1").Range("A1").Resize(UBound(top0M, 1), UBound(top0M, 2)).Value = top0M