Transpose data in an two dimensional array

I know that if you use Redim Preserve, you can only change the last dimesion of the array.
So I've now got a populated array that I want to paste into an excel sheet.  The problem is that the rows and columns are transposed.
Is there an easy way (memory and speed efficient) to transpose the array's info?
I thought the following could work, but doesn't....
      Range(Cells(20, 2), Cells(25, 3)) = Transpose(Arr)
Arr is a two dimensional array Arr(2,6) and I want it to be Arr(6,2)
Thanks
JL
jlfourieAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Using
Range(Cells(20, 2), Cells(25, 3)) = Application.Transpose(Arr)

Open in new window

may work, but I prefer to loop and create a new array:
Function TransposeGetRows(varData) As Variant
   Dim lngRow As Long, lngCol As Long
   Dim varOut()
   ReDim varOut(1 To UBound(varData, 2) + 1, 1 To UBound(varData, 1) + 1)
   For lngRow = LBound(varData, 2) To UBound(varData, 2)
      For lngCol = LBound(varData, 1) To UBound(varData, 1)
         varOut(lngRow + 1, lngCol + 1) = varData(lngCol, lngRow)
      Next lngCol
   Next lngRow
  TransposeGetRows = varOut
End Function

Open in new window


which assumes the input array is 0 based.

0
 
jlfourieAuthor Commented:
Thanks, I just got to the same application.transpose solution and wanted to update my question.  And it works well and fast.
I might try your other option, if you think it will be much quicker (memory wise).
Regards
JL
0
 
jlfourieAuthor Commented:
Thanks for a speedy response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.