Dimention & manipulation of Variant array

[muster]

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.

 

[Bob Phillips]

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.

 

[Tom Ogilvy]

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

 

[Alan Beban]

That is right, because a range has rows and columns, so it always allows for
multiples



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

 

[Alan Beban]

Another method of getting a one-dimensional array from a two-dimensional
single row array is

vRow = Application.Index(vRow, 1, 0)

Alan Beban