Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dimention & manipulation of Variant array
I use variant array (ie. dim array as variant) to read a range from a
sheet into to an array to avoid loops. I found the array is always 2D, even when I read a single row or column. Is there a way (or another way if I did wrong) to make it 1D array? Further more, is it possible to get single row or column from this kind of 2D array quickly, like array(1) or array(1, *)? Hopefull I made it clear, thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dimention & manipulation of Variant array
"muster" wrote in message oups.com... I use variant array (ie. dim array as variant) to read a range from a sheet into to an array to avoid loops. I found the array is always 2D, even when I read a single row or column. Is there a way (or another way if I did wrong) to make it 1D array? That is right, because a range has rows and columns, so it always allows for multiples Further more, is it possible to get single row or column from this kind of 2D array quickly, like array(1) or array(1, *)? Don't think so, you need to loop through if you want it single dimension. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dimention & manipulation of Variant array
For a single row, you use application.Transpose twice. For a single column,
you use it once: Sub efg() Dim vr As Variant, vc As Variant Dim vRow As Variant, vCol As Variant Dim rngRow As Range, rngColumn As Range Set rngRow = Range("A1:Z1") Set rngCol = Range("A1:A20") vRow = rngRow.Value vr = Application.Transpose(Application.Transpose(vRow)) Debug.Print UBound(vr, 1) vCol = rngCol.Value vc = Application.Transpose(vCol) Debug.Print UBound(vc, 1) End Sub -- Regards, Tom Ogilvy "muster" wrote: I use variant array (ie. dim array as variant) to read a range from a sheet into to an array to avoid loops. I found the array is always 2D, even when I read a single row or column. Is there a way (or another way if I did wrong) to make it 1D array? Further more, is it possible to get single row or column from this kind of 2D array quickly, like array(1) or array(1, *)? Hopefull I made it clear, thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dimention & manipulation of Variant array
Bob Phillips wrote:
"muster" wrote in message oups.com... I use variant array (ie. dim array as variant) to read a range from a sheet into to an array to avoid loops. I found the array is always 2D, even when I read a single row or column. Is there a way (or another way if I did wrong) to make it 1D array? That is right, because a range has rows and columns, so it always allows for multiples Further more, is it possible to get single row or column from this kind of 2D array quickly, like array(1) or array(1, *)? Don't think so, you need to loop through if you want it single dimension. If you want MyArray2 to be the nth column of MyArray1 MyArray2 = Application.Index(MyArray1, 0, n) For the nth row MyArray2 = Application.Index(MyArray1, n, 0) In the second case MyArray2 will be one-dimensional Alan Beban |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dimention & manipulation of Variant array
Another method of getting a one-dimensional array from a two-dimensional
single row array is vRow = Application.Index(vRow, 1, 0) Alan Beban Tom Ogilvy wrote: For a single row, you use application.Transpose twice. For a single column, you use it once: Sub efg() Dim vr As Variant, vc As Variant Dim vRow As Variant, vCol As Variant Dim rngRow As Range, rngColumn As Range Set rngRow = Range("A1:Z1") Set rngCol = Range("A1:A20") vRow = rngRow.Value vr = Application.Transpose(Application.Transpose(vRow)) Debug.Print UBound(vr, 1) vCol = rngCol.Value vc = Application.Transpose(vCol) Debug.Print UBound(vc, 1) End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
ReDim Object array as parameter of Variant array | Excel Programming | |||
Variant Array | Excel Programming | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming |