Non-contiguous range into 2D array

[ButtFace]

Hi gurus,

Happy Friday!

I am trying to find a quick to read a non-contiguous range into a 2D array without iterating over every element.

Basically I'm trying to dump each area of the range into each column of the array without iterating over every element for the sake of performance.

I was trying an approach similar to below (which is syntactically incorrect) but was wondering if an approach like below is even possible?

The resulting array will ultimately be used to populate a multi-column combobox that I'm writing an autocomplete/search-as-you-type function for.

Code below is untested I just wrote it off the top of my head in code tags.

dim h as long, w as long, l as long
dim rSomeUnion as range, rArea as range
dim arrOut as Variant

set rSomeUnion = application.union( _
    sheet1.range("a1:a200"), _
    sheet1.range("c1:c150"), _
    sheet1.range("e1:e100"), _
    sheet1.range("g1:g250"))
    
'determine array dimensions
w = rSomeUnion.areas.count 'array width
for l = 1 to rSomeUnion.areas.count
    h = iif(rSomeUnion.areas(l).rows.count > h, rSomeUnion.areas(l).rows.count, h) 'array max height
next l

'populate -- here is where i don't know the correct syntax?
'basically i'm trying to read each area of the union into each array column in a single statement. Not sure if this is possible?
redim arrOut(h, w)
for l = 1 to w
    arrOut(1 to rSomeUnion.areas(l).rows.count, l) = rSomeUnion.areas(l).value2
next l

 

[Fluff]

If you want it as a 2D array you will need the same number of rows for all columns, try

   Dim Ary As Variant
   Ary = Sheet1.Range("A1:G250").Value2
   Ary = Application.Index(Ary, Evaluate("row(1:" & UBound(Ary) & ")"), Array(1, 3, 5, 7))
   Me.ComboBox1.List = Ary

 

[ButtFace]

Works like a charm!

Elegant solution with the worksheet function. Didn't know there was an array-form of Index.

Thanks again everyone, happy Friday!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Rick Rothstein]

Since you know your are setting the number of rows to 250, you can do away with creating the interim array and assign the values from the Index function directly to the ComboBox List property using a single line of code...

ComboBox1.List = Application.Index(Cells, [ROW(1:250)], [{1,3,5,7}])

 

[ButtFace]

Since you know your are setting the number of rows to 250, you can do away with creating the interim array and assign the values from the Index function directly to the ComboBox List property using a single line of code...

ComboBox1.List = Application.Index(Cells, [ROW(1:250)], [{1,3,5,7}])

Concise, I like it.

In my use case this is going to be a floating combobox that covers ActiveCell and shows/hides depending on the column it intersects. Depending on column, dataset dimensions for cbo.List will vary as will the columns that are displayed in cbo.List, so I can't really hard code row count or columns returned by Index.

I got it from here though, thanks for all the help.

As always, you guys are the best!

 

[Rick Rothstein]

If you are going to have the columns vary, then you might consider using Split("1 3 5 7") in Fluff's code instead of Array(1,3,5,7) as you can concatenate the string argument to the Split function together "on the fly" whereas you cannot do that with the Array function (the array from either the Split function or the Array function do the same thing when used in the Index function's third argument).