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 ?
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!
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)