Transpose an Array

**Aphyx** · 07-28-2012, 09:57 AM

Good morning folks! Simple question that I suspect doesn't have a simple answer.

How do I TRANSPOSE the values in an array?

I have an array that I needed to ReDim Preserve and it was build up dynamically. I did so in a loop using a Variable for the array size which forced me to place the part of the array that contained the variable on the right of the Variable

Example: ReDim Preserve NewData(1 To 5, 1 To k) As Variant

When placing the data on the new sheet I want the data to be: NewData(1 To k, 1 To 5) TRANSPOSED

Any help would be appreciated.

---------- Post added at 09:57 AM ---------- Previous post was at 09:49 AM ----------

GOT IT!!! For those who may also be wondering...

tansData = Application.WorksheetFunction.Transpose(NewData)

**smuzoen** · 07-28-2012, 10:08 AM

I am not sure I am clear on what you EXACTLY want - you know you do not have to loop through a range of cells to create an array. For example if you have values in A1:E25 you can place that into an array with

Please Login or Register  to view this content.

This will create a two dimensional array with myArr(1 to 25, 1 to 5)
Then to transpose this into A26:Y30 you would use

Please Login or Register  to view this content.

When you say

NewData(1 To k, 1 To 5)

you cannot redimension the first value of an array.
Using the code above the values in A1:E25 will get transposed into A26:Y30.
Is that what you mean?

**smuzoen** · 07-28-2012, 10:12 AM

My post was duplicated - see above

**smuzoen** · 07-28-2012, 10:49 AM

If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

**jindon** · 07-28-2012, 10:51 AM

Assuming NewData is filled properly

Please Login or Register  to view this content.

**Cutter** · 07-28-2012, 11:45 AM

@ Aphyx

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

**Cutter** · 07-28-2012, 11:45 AM

@ Aphyx

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.