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.

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)



Bob wrote:

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

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" wrote:

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)



Bob wrote:

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

Although both my variables were dimmed as Variants, they were both arrays.

myArr1 = ActiveSheet.Range("a1:d2").Value
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)




Bob wrote:

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" wrote:

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)



Bob wrote:

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


--

Dave Peterson

Bob wrote:
. . . 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

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" wrote:

Bob wrote:
. . . 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

Bob,

http://home.pacbell.net/beban

Alan Beban

Bob wrote:
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

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

Bob wrote:
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" wrote:


Bob wrote:

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