Extract One column from multi-column array

[Shred Dude]

I have a routine that creates a 3 column variant array. I will typically return that array to a spreadsheet in its entirety. However, under certain circumstances, I may only want to return the 3rd column of that array, for example.

I use a select case structure to identify that circumstance, and then create a new one column array by looping through the 3-column array, extracting just the values from the third column, and then return that new one column array to the spreadsheet.

eg:

[VBA] 'get just the last column
ReDim y(UBound(x, 1))
For r = 0 To UBound(y) - 1
y(r) = x(r, 2)
Next r[/VBA]

Is there a better way? I haven't been able to figure out how to just grab all of the third column at once to avoid the looping. I considered writing the three column array to the sheet and then deleting the first two columns in the spreadhsheet. That's pretty fast, but I was wondering if there's a way I can avoid that?

I could modify the routine that creates the 3-column array to accept an optional passed variable that would cause it to only return a one-column array when needed.

But I'm curious, is there a way to "resize" a variant array on the fly?? Redim preserve with just the third column???

Any insight most appreciated!

Thanks,

Shred

[mikerickson]

[VBA]Dim myThirdColumn As Variant

myThirdColumn = Application.Index(myArray, , 3)[/VBA]

[Shred Dude]

Thanks for the quick reply...

I had to modify slightly to:

[VBA]Dim y As Variant
y = Application.Index(x, , 2)[/VBA]

My array "x" is zero based so third column is 2. the "3" was causing an error.

I haven't found any reference to this function in Help files though. I can't come across it in my object browser.

It appears to return a 1-based, two dimensional array. That was throwing me for a while. I finally got the results to the sheet though.

So, the first value is found in y(1,1). y(1) throws a subscript out of range error.

Can you point me to reference material on this?

Thanks again. Exactly what I was wanting!

[Shred Dude]

My bad, it really is a two column array. I forgot I had consolidated the first two columns in another routine. Your "3" would clearly have worked on a three column array.

[Shred Dude]

WorksheetFunction.Index Method

I guess I wasn't aware you could call Worksheetfunction members with just Application.

Thanks again!