• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • 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)
Thanks
JL
0
jlfourie
Asked:
jlfourie
  • 2
1 Solution
 
Rory ArchibaldCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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