-
Extract One column from multi-column array
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
-
[VBA]Dim myThirdColumn As Variant
myThirdColumn = Application.Index(myArray, , 3)[/VBA]
-
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!
-
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.
-
WorksheetFunction.Index Method
I guess I wasn't aware you could call Worksheetfunction members with just Application.
Thanks again!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules