Solved

# Transpose data in an two dimensional array

Posted on 2011-03-22
234 Views
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
Question by:jlfourie
• 2

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
Using
``````Range(Cells(20, 2), Cells(25, 3)) = Application.Transpose(Arr)
``````
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
``````

which assumes the input array is 0 based.

0

Author Comment

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

Author Closing Comment

Thanks for a speedy response.
0

## Join & Write a Comment Already a member? Login.

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

#### 762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!