Shred Dude
05-30-2010, 09:23 AM
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:
'get just the last column
ReDim y(UBound(x, 1))
For r = 0 To UBound(y) - 1
y(r) = x(r, 2)
Next r
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
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:
'get just the last column
ReDim y(UBound(x, 1))
For r = 0 To UBound(y) - 1
y(r) = x(r, 2)
Next r
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