• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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)
  • 2
1 Solution
Rory ArchibaldCommented:
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.

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).
jlfourieAuthor Commented:
Thanks for a speedy response.
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now