WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Transpose

VBA Transpose

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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 a 4 X 3 array, then it becomes a 3 X 4 array.

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

vba-transpose

#1 – VBA Transpose Using TRANSPOSE Formula

Like how we use TRANSPOSE in excel similarly, we can use the TRANSPOSE formula in VBA too. We don’t 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 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

transpose example 1.3

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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

Ok, we are done with TRANSPOSE formula coding. Now run the code to see the result in 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 Paste Special method. Consider the same data for this example as well.

transpose example 1.1

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

example 2.1

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

example 2.2

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

example 2.3

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

example 2.4

This 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 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 the 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 a 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
2 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More