VBA Transpose

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

We have seen the Transpose function in the Excel worksheet when we paste any data table to the worksheet. Transpose changes the position of rows and columns, i.e., rows become columns, and columns become rows in a Data Table. 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 a data manipulation technique that almost all users use in Excel. The process of converting horizontal data to vertical and vertical data to horizontal is called “Transpose” in Excel. We are sure you must be familiar with transposing in a regular worksheet. This article 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 MethodPaste Special MethodPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more.

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

Let us see some examples of transposing columns to rows in VBA.

VBA Transpose

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Transpose (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

#1 – VBA Transpose Using TRANSPOSE Formula

Like how we use TRANSPOSE in excelTRANSPOSE In ExcelThe TRANSPOSE function in excel helps rotate (switch) the values from rows to columns and vice versa. Being a part of the Excel lookup and reference functions, its purpose is to organize the data in the desired format. To execute the formula, the exact size of the range to be transposed is selected and the CSE key (“Control+Shift+Enter”) is pressed. read more similarly, we can also not have a TRANSPOSE formula in VBA, so we need to use it under the Worksheet function class.

For example, look at the below data image.

transpose example 1.1

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
transpose example 1.2

Step 2: First, we must decide where to transpose the data. In this, we have chosen to transpose from cell D1 to H2. So, enter the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more as Range (“D1: H2”).Value =

Code:

Sub Transpose_Example1()

Range("D1:H2").Value =

End Sub
transpose example 1.3

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

transpose example 1.4

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
transpose example 1.5

We have completed the TRANSPOSE formula coding. Now, run the code to see the result in the D1 to H2 range of cells.

transpose example 1.6

As we have seen in 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 the Paste Special method. Consider the same data for this example as well.

transpose example 1.1

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

Code:

Sub Transpose_Example2()

Range("A1:B5").Copy

End Sub
example 2.1

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

Code:

Sub Transpose_Example2()

Range("A1:B5").Copy

Range("D1").

End Sub
example 2.2

Once the desired destination cell is selected, we must select the “Paste Special” method.

example 2.3

With Paste Special, we can perform all our actions with regular Paste Special methods in a worksheet.

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

Code:

Sub Transpose_Example2()

Range("A1:B5").Copy

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

End Sub
example 2.4

It will also transpose the data like the previous method.

example 2.5

Like this, we can use the TRANSPOSE formula or Paste Special method to transpose the data to switch rows to columnsSwitch Rows To ColumnsRows can be transposed to columns by using paste special method and the data can be linked to the original data by simply selecting ‘Paste Link’ form the paste special dialog box. It could also be done by using INDIRECT formula and ADDRESS functions.read more and columns to rows.

Things to Remember

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

Recommended Articles

This article has been a guide to VBA Transpose. Here, we discussed transposing columns to rows using VBA code with examples and a downloadable Excel template. Below are some useful articles related to VBA: –