Is it possible to load dimensions of an Array independently?

[polarisking]

Situation: 500,000 rows with 12 columns. I need 3 of the columns, and they're not contiguous.

I want to load an array(500000,3), but I want to load each dimension separately - is such an ask possible?

 

[Hui]

Yes,
But you'll either need to write a routine to do it or get a pre-existing routine of the net

Have a look at Chip Pearon's functions here:

VBA Arrays

www.cpearson.com

Specifically the GetColumn function

Read the post carefully as you will need to load a lot of the code

 

[polarisking]

Yes,
But you'll either need to write a routine to do it or get a pre-existing routine of the net

Have a look at Chip Pearon's functions here:

VBA Arrays

www.cpearson.com

Specifically the GetColumn function

Read the post carefully as you will need to load a lot of the code

Thank you, Hui. I've enjoyed Chip's material for quite some time.

What I did was create N 1 column arrays + a "master array" with N columns, loaded each individually, then since they're all the same length, filled the master array with direct assignments to the rows in each of the individual arrays. Very fast, and it did the trick.

 

[Fluff13]

Whilst this doesn't load the dimensions separately, you could use

Sub polarisking()
   Dim Ary As Variant
   Dim Usdrws As Long
   Usdrws = Range("A" & Rows.Count).End(xlUp).Row
   Ary = Application.Index(Range("A1:AF" & Usdrws).Value2, Evaluate("row(1:" & Usdrws & ")"), Array(1, 8, 11, 15))
End Sub

 

[polarisking]

Whilst this doesn't load the dimensions separately, you could use

Sub polarisking()
   Dim Ary As Variant
   Dim Usdrws As Long
   Usdrws = Range("A" & Rows.Count).End(xlUp).Row
   Ary = Application.Index(Range("A1:AF" & Usdrws).Value2, Evaluate("row(1:" & Usdrws & ")"), Array(1, 8, 11, 15))
End Sub

Fluff13, this is incredibly elegant. Even though I've considered myself quite conversant with VBA and Excel constructs, I'm having difficultly understanding exactly what

Ary = Application.Index(Range("A1:AF" & Usdrws).Value2, Evaluate("row(1:" & Usdrws & ")"), Array(1, 8, 11, 15))

is doing.

 

[Fluff13]

It's loading all the rows of data from columns A,H,K & O into an array

 

[p45cal]

Fluff13, this is incredibly elegant.

Be aware, that if you're looking for speed, looping is faster;
This:

Sub polarisking()
Dim Ary As Variant
Dim Usdrws As Long
Usdrws = Range("A" & Rows.Count).End(xlUp).Row
Ary = Application.Index(Range("A1:AF" & Usdrws).Value2, Evaluate("row(1:" & Usdrws & ")"), Array(1, 8, 11, 15))
End Sub

is 7 times slower than this!:

Sub polarisking2()
Dim Ary As Variant
Dim Usdrws As Long
Usdrws = Range("A" & Rows.Count).End(xlUp).Row
ReDim Ary(1 To Usdrws, 1 To 4)
c1 = Range("A1:AF" & Usdrws).Columns(1).Value
c2 = Range("A1:AF" & Usdrws).Columns(8).Value
c3 = Range("A1:AF" & Usdrws).Columns(11).Value
c4 = Range("A1:AF" & Usdrws).Columns(15).Value
For i = 1 To Usdrws
  Ary(i, 1) = c1(i, 1)
  Ary(i, 2) = c2(i, 1)
  Ary(i, 3) = c3(i, 1)
  Ary(i, 4) = c4(i, 1)
Next i
End Sub

Also, if there are dates, make sure you use .Value2 as suggested, because in the first macro above if you use just .Value, those dates will be converted to strings, and when they're written back to the sheet, their interpretation depends on the locale.
The second macro will keep dates as dates, so you can use .Value (but don't later use Application.Transpose or they'll be strings again!)