Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Transposing an Array in VBA

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.

Contact Form

Name

Email *

Message *