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.

"muster" <muster@gmail.com> wrote in message
news:1152541171.877506.201480@p79g2000cwp.googlegroups.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.

Bob Phillips wrote:
> "muster" <muster@gmail.com> wrote in message
> news:1152541171.877506.201480@p79g2000cwp.googlegroups.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

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.
>
>

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
>