几个不连续的列进入数组 [英] several non contiguous columns into array

问题描述

我尝试将4列尽可能高效地加载到数组中.
我尝试过

I try to load as efficiently as possible 4 columns to an array.
I tried

dim ar
ar = sheet1.Range("C2:C2681,G2:G2681,J2:J2681,T2:T2681")

，但只有第一列被加载到数组中.
我也尝试过

but only the first column is loaded into the array.
I also tried

    ar = .Range("C2:T" & lastRow)
    ar = Application.Index(ar, , Array(1, 5, 19))

但是这给了我一个类型不匹配的错误.

but that gives me a type mismatch error.

为此目的有任何巧妙的把戏吗?

Any clever trick for this purpose ?

推荐答案

您始终可以将这些列存储在锯齿状的数组，一个数组数组.在VBA中，语法实际上非常容易.您可以将一个范围的所有.Value(以数组的形式)存储在另一个(以前为灰色)数组中.

You could always store those columns within a jagged array, an array of arrays. The syntax is actually pretty easy in VBA; you can store all the .Values of a range (in the form of an array) inside of another (previously dimmed) array.

当您使用包含多个子范围(连续或不连续)的范围时，可以通过循环访问该范围的

When you're working with a range that includes several sub-ranges (contiguous or not), you can access them separately by looping on that range's .Areas property.

您唯一需要注意的是索引，因为语法有点时髦，在您的特定示例中，您不必从第一行开始.

The only thing that you have to be careful about is the indices because the syntax is a little funky and in your particular example you don't start with the first row.

Option Explicit

Sub NonContiguousRanges()

    Dim rng As Range
    Set rng = Range("C2:C20, G2:G20, J2:J20, T2:T20")

    Dim jagged As Variant
    ReDim jagged(1 To rng.areas.count)

    Dim i As Long
    For i = 1 To rng.areas.count
        jagged(i) = rng.areas(i).Value2
    Next i


    '=-~ examples of accessing the values ~-='

    'first value, C2
    MsgBox jagged(1)(1, 1)

    'last  value, T20
    MsgBox jagged(4)(19, 1)
    MsgBox jagged(UBound(jagged))(UBound(jagged(UBound(jagged))), 1)

End Sub

我的意思是只看所有这些UBound ......把它弄对我有点头疼！

I mean just look at all those UBounds... gave me a bit of a headache just getting it right!

这篇关于几个不连续的列进入数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！