VBA Transpose

We have seen transpose function in excel worksheet when we paste any data table to the worksheet, what transpose does is that it changes the position of rows and columns i.e. rows become columns and columns becomes rows in a data table, now since it is a worksheet function in VBA we use it with the Application.worksheet method in VBA.

How to Transpose in VBA?

Switching rows and columns is one of the data manipulation techniques almost all the users do in excel. The process of converting horizontal data to vertical and vertical data to horizontal is called as “Transpose” in excel. I am sure you must be familiar with Transposing in a regular worksheet, in this article we will show you how to use the transpose method in VBA coding.

We can transpose in VBA using two methods.

1. Transpose Using TRANSPOSE Formula.
2. Transpose Using Paste Special Method.

When we are transposing we are swapping rows to columns and columns to rows. For example, if the data is in 4 X 3 array then it becomes 3 X 4 array.

Let’s see some examples to transpose column to row in VBA.

#1 – VBA Transpose Using TRANSPOSE Formula

Like how we use TRANSPOSE in excel similarly we can use TRANSPOSE formula in VBA too. We don’t have a TRANSPOSE formula in VBA, so we need to use under Worksheet Function class.

For an example look at the below data image.

We will try to transpose this array of values. Follow the below steps to transpose the data.

Step 1: Start the subprocedure

Code:

Sub Transpose_Example1()

End Sub

Step 2: First we need to decide where we are going to transpose the data. In this, I have chosen to transpose from cell D1 to H2. So, enter the VBA code as Range (“D1: H2”).Value =

Code:

Sub Transpose_Example1()

Range("D1:H2").Value =

End Sub

Step 3: Now in the above-mentioned range we need the value of range A1 to B5. To arrive at this open “Worksheet Function” class and select the “Transpose” formula.

Step 4: In Arg 1 supply the data source range i.e. Range (“A1: D5”).

Code:

Sub Transpose_Example1()

Range("D1:H2").Value = WorksheetFunction.Transpose(Range("A1:D5"))

End Sub

Ok, we are done with TRANSPOSE formula coding. Now run the code to see the result in D1 to H2 range of cells.

As we have seen the above image it has converted the range of cells from columns to rows.

#2 – VBA Transpose Using Paste Special Method

We can also transpose using Paste Special method. Consider the same data for this example as well.

The first thing we need to do to transpose is to copy the data. So write the code as Range(“A1: B5”).Copy

Code:

Sub Transpose_Example2()

Range("A1:B5").Copy

End Sub

The next thing is we need to decide where we are going to paste the data. In this case, I have chosen D1 as the desired destination cell.

Code:

Sub Transpose_Example2()

Range("A1:B5").Copy

Range("D1").

End Sub

Once the desired destination cell is selected we need to select “Paste Special Method”.

With paste special, we can perform all the actions we have with regular paste special methods in a worksheet.

Ignore all the parameters and select the last parameter i.e. Transpose and make this as TRUE.

Code:

Sub Transpose_Example2()

Range("A1:B5").Copy

Range("D1").PasteSpecial Transpose:=True

End Sub

This will also transpose the data like the previous method.

Like this, we can use the TRANSPOSE formula or Paste Special method to transpose the data to switch rows to columns and columns to rows.

Things to Remember

• If we are using the TRANSPOSE worksheet function it is mandatory to calculate a number of rows and columns to transpose the data. If we have 5 rows and 3 columns then while transposing it becomes 3 rows and 5 columns.
• If you want the same formatting while using paste special then you have to use Paste Type argument as “xlPasteFormats”

You can download this VBA Transpose Excel Template from here – VBA Transpose Excel Template

Recommended Articles

This has been a guide to VBA Transpose. Here we discussed how to transpose columns to row using VBA Code with examples and downloadable excel template. Below are some useful articles related to VBA –

• VBA IIF
• Find and Replace Function in VBA
• ArrayList in VBA Excel
• Not Equal in VBA