Transposing rows/columns in an array

[Guest]

Is it possible to transpose a given "row" or "column" within a 2-dimensional
array (similar to the way it can be done in a worksheet)? If so, can someone
kindly point me to VBA code snipets that demonstrate this capability.

Thanks in advance for any guidance.

 

[Dave Peterson]

Depending on the version of excel that you're using and the number of elements
in your array, you could use application.transpose:

Dim myArr1 As Variant
Dim myArr2 As Variant

myArr1 = ActiveSheet.Range("a1:d2").Value
myArr2 = Application.Transpose(myArr1)

 

[Guest]

Dave,

As always, thanks for your help! I'm using Excel 2003.

Based on your code below, are you really transposing an array? Forgive my
ignorance, but I thought you always needed to declare an upper bound for an
array.

Also, the problem I'm trying to solve involves a 2-dimensional array, and a
specific "row" within that array that I need to transpose into a "column".
Is that doable?

Thanks again,
Bob

 

[Dave Peterson]

Although both my variables were dimmed as Variants, they were both arrays.
is a quick way to create a 2 row by 4 column array.

If I only had a specific row to pick out, I'd just assign the values to the
other array:

Dim myArr1(1 to 99, 1 to 2) as long 'say
dim myArr2(1 to 2, 1 to 1) as long
dim iRow as long
irow = 44
myarr2(1,1) = myarr1(irow,1)
myarr2(2,1) = myarr1(irow,2)

I'm not sure I'd even bother with the second dimension of myArr2.
Dim myArr1(1 to 99, 1 to 2) as long 'say
dim myArr2(1 to 2) as long
dim iRow as long
irow = 44
myarr2(1) = myarr1(irow,1)
myarr2(2) = myarr1(irow,2)

 

[Alan Beban]

. . . the problem I'm trying to solve involves a 2-dimensional array, and a
specific "row" within that array that I need to transpose into a "column".
Is that doable?

myArray2 = Application.Transpose(Application.Index(myArray1, n, 0))

where n is the number of the "row" of myArray1 to be transposed.

Alan Beban

 

[Guest]

Alan,
Thanks for your help!
BTW, I understand that you have a downloadable file with 20+ array
manipulation UDFs. Can you kindly provide me with the URL?
Thanks again,
Bob

 

[Alan Beban]

Bob,

http://home.pacbell.net/beban

Alan Beban

 

[Alan Beban]

By the way, the syntax for transposing a row (say, row n) using the
functions downloadable from the site is

myArray2 = ArrayTranspose(RowVector(myArray1, n))

The ArrayTranspose function operates on larger arrays than the built-in
TRANSPOSE function will accept, and retains the type of the array
transposed--the built-in function returns a Variant() array without
regard to the type of array transposed. And the RowVector function
accepts larger arrays than the built-in INDEX function will accept. The
tradeoff, of course, is that the built-in functions are almost always,
if not always, faster than the UDF's.

Alan Beban