several non contiguous columns into array

Question

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 ?

Put them into 4 different arrays. If you want to then loop them and put them in one array. — Scott Craner, Sep 19 '18 at 14:29
In a meeting, on my phone. I like jeeped's answer better anyways. — Scott Craner, Sep 19 '18 at 14:39
@ScottCraner I gotchu my man, added one with the type of answer you were thinking of — Marcucciboy2, Sep 20 '18 at 17:28

score 2 · Answer 1 · answered Sep 19 '18 at 14:35

ReDim with Preserve will only allow you to modify the last rank but you could shift the array elements' values first.

dim ar as variant, i as long

ar = sheet1.Range("C2:T2681").value

for i=lbound(ar, 1) to ubound(ar, 1)
    ar(i, 2) = ar(i, 5)   'move column G to 2nd
    ar(i, 3) = ar(i, 8)   'move column J to 3rd
    ar(i, 4) = ar(i, 18)  'move column T to 4th
next i

redim preserve ar(lbound(ar, 1) to ubound(ar, 1), lbound(ar, 2) to 4)

Marcucciboy2 · Accepted Answer · 2018-11-22T22:56:40.147

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

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

several non contiguous columns into array

2 Answers2

Linked

Related