Non-contiguous range into 2D array

ButtFace

Board Regular
Joined
Oct 16, 2015
Messages
63
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,668
Office Version
  1. 365
Platform
  1. Windows
If you want it as a 2D array you will need the same number of rows for all columns, try
VBA Code:
   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

Board Regular
Joined
Oct 16, 2015
Messages
63
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,668
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,369
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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...
VBA Code:
ComboBox1.List = Application.Index(Cells, [ROW(1:250)], [{1,3,5,7}])
 

ButtFace

Board Regular
Joined
Oct 16, 2015
Messages
63
Office Version
  1. 2016
Platform
  1. Windows
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...
VBA 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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,369
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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).
 

Forum statistics

Threads
1,141,863
Messages
5,709,074
Members
421,613
Latest member
wyzco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top