Using arrays with ranges

[agibsonsw]

Hello.

I know that I can use a Variant to grab an Excel range of data, and eventually feed it back into a range of the same size:

Dim arr As Variant
arr = Range("A1:H12").Value
'navigate the array using arr(x,y)
Range("A14:H25").Value = arr

But is it possible to grab a "row" or "column" of data from the array and put it into a range, without looping?
Is it possible to grab the Excel range of data and chuck it into a 3D array? I believe it would always coerce the
Variant into a 2D array, but perhaps there is a way to then copy this array into another (3D) array, without looping?

Andy.

[HansV]

You can assign a two-dimensional array to a "smaller" range, e.g. a single-row column or a single-column row. The range will be filled with the "top left corner" of the array:

Dim arr As Variant
arr = Range("A1:H12").Value
' Fill a single row with the values of A1:H1
Range("A14:H14").Value = arr
' Fill a single column with the values of A1:A12
Range("K5:K16").Value = arr

But I don't see an easy way to refer to an arbitrary sub-array without looping, nor to copy the array into a three-dimensional array without looping.

[agibsonsw]

Thanks @Hans.

Didn't think so

[Jim Cone]

It is possible to extract any row or column from an array using the Index worksheet function...

Range("A14:H14").Value = Application.WorksheetFunction.Index(arr, 3, 0)
(extracts the the third row)
Range("K5:K16").Value = Application.WorksheetFunction.Index(arr, 0, 3)
(extracts the third columh)

The trick is to specify 0 for row to return the entire column and vice versa.
'---
Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2" onclick="window.open(this.href);return false; (Dropbox)

[HansV]

Thanks, Jim - I always forget about using 0 with the Index function.

[Jim Cone]

Hans,
I come here to remember the things I forgot.
You are ahead of me about 20 to 1.

I have been working on a vba Date Picker that requires extraction of columns from an array.
It's easy to remember something when you have just done it. <grin>
Jim Cone
'---
Edit:
I looked at that second line again and it may not convey what I intended which is...
I learn about 20 things here for every item I might contribute.
JBC

[agibsonsw]

It is possible to extract any row or column from an array using the Index worksheet function...

Range("A14:H14").Value = Application.WorksheetFunction.Index(arr, 3, 0)
(extracts the the third row)
Range("K5:K16").Value = Application.WorksheetFunction.Index(arr, 0, 3)
(extracts the third columh)

The trick is to specify 0 for row to return the entire column and vice versa.
Jim Cone

All I can say is Wow! and thank you

I was having a look at your article about finding the last row: I've always thought I was alone in considering this a conundrum! I've known a number of approaches - none totally satisfactory - and, as you indicate, I've always produced an acceptable method for the particular circumstance.

This is one of those questions that a human is able to answer very quickly - and revise the answer if asked to find "the last row of the worksheet, the last row within this area or table", etc. - yet is very hard to to describe programmatically.

Regards, Andy.