In this post I will explain you a quick way to transpose an array. Sometimes we need to transpose our arrays before put it to worksheet. You can easily use below method for those situations.
So let's consider the below sample data for this example.
I have this data in Sheet10 of my Excel workbook.
So let's declare our variables and set the worksheet first.
|
Dim WS As Worksheet Set WS = Worksheets("Sheet10") Dim All_Data() As Variant |
Now we can put above data to declared array.
|
All_Data = WS.Range("A1", "J4") |
So we have put all the data to All_Data array.
Next we need to add new worksheet to put our transposed array.
|
Worksheets.Add |
Then you can transpose the array and put it to new worksheet as follows.
|
ActiveSheet.Range("A1", Range("A1").Offset(UBound(All_Data, 2) - 1, 3)).Value = Application.Transpose(All_Data) |
So below is the full code to transpose an array.
|
Dim WS As Worksheet Set WS = Worksheets("Sheet10") Dim All_Data() As Variant All_Data = WS.Range("A1", "J4") Worksheets.Add ActiveSheet.Range("A1", Range("A1").Offset(UBound(All_Data, 2) - 1, 3)).Value = Application.Transpose(All_Data) |
And you will get below result after running the macro.
