EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Lookup & Reference Functions VBA Transpose
Secondary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
    • Compare Two Columns in Excel using VLOOKUP
    • VLOOKUP with Different Sheets

VBA Transpose

By Madhuri ThakurMadhuri Thakur

VBA Transpose

VBA Transpose

Being a Microsoft Excel user, you may frequently have used transpose function which allows the swapping between rows and columns for an array. Converting rows into column and columns into rows is what a transpose function does for you in excel. The number of rows becomes a number of columns and vice versa. Means, if you have 2 rows and 3 columns in your array, after transposing it will change to an array with 3 rows and 2 columns. In this tutorial, we will go through VBA Transpose which will allow you to automate the transpose method you to use in Excel.

Syntax of Transpose in Excel VBA

VBA Transpose has the following syntax:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of Transpose

Where,

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)

Arg1:  It is a required argument which is nothing but a range of cells we wanted to transpose (i.e. array).

The first part of the syntax is nothing but an expression under which Transpose function can be used. For example, WorksheetFunction.

How to Use Excel VBA Transpose?

We will learn how to use a Transpose with few examples in Excel VBA.

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

Example #1 – VBA Transpose of One-dimensional Array

Suppose you are working on a data with lists (which is a one-dimensional array) like names of an employee given (“Lalit”, “Sneha”, “Ethyl”, “John”, “Cory”) and you want this list to be pasted in excel cell wise. Let’s see how we can do this.

Follow the below steps to use Transpose in VBA.

Step 1: Insert a new module and define a new sub-procedure to create a macro in VBA.

Code:

Sub Trans_ex1()

End Sub

VBA Transpose Example 1-1

Step 2: Define a new variable which can hold your one-dimensional array.

Code:

Sub Trans_ex1()

Dim Arr1 As Variant

End Sub

VBA Transpose Example 1-2

Step 3: Define the list as an array using the Array function. This would be the list you wanted to paste in your excel sheet.

Code:

Sub Trans_ex1()

Dim Arr1 As Variant
Arr1 = Array("Lalit", "Sneha", "Ethyl", "John", "Cory")

End Sub

VBA Transpose Example 1-3

Step 4: Now use the Range.Value method to define the range in which these values you wanted to get pasted.

Code:

Sub Trans_ex1()

Dim Arr1 As Variant
Arr1 = Array("Lalit", "Sneha", "Ethyl", "John", "Cory")
Range("A1:A5").Value =

End Sub

VBA Transpose Example 1-4

Step 5: Use Application.WorksheetFunction.Transpose on the given array to be able to transpose the list defined under Arr1 variable.

Code:

Sub Trans_ex1()

Dim Arr1 As Variant
Arr1 = Array("Lalit", "Sneha", "Ethyl", "John", "Cory")
Range("A1:A5").Value = Application.WorksheetFunction.Transpose(Arr1)

End Sub

VBA Transpose Example 1-5

Here, we have defined a code which will allow the system to transpose the data in list array called Arr1 and then store it under cell A1:A5 on the active worksheet.

Step 6: Hit F5 or Run button under VBE to run this code and see the output under active excel spreadsheet.

Result of Example 1-6

You can see that the given array of the list is transposed into one single column and stored under cell A1 to A5.

Example #2 – VBA Transpose of two-dimensional Array

Suppose you have a two-dimensional array of employees and their salaries as below:

VBA Transpose Example 2

This is a two-dimensional data with six rows and two columns. After transposing, it would be an array with two rows and six columns.

Follow the below steps to use Transpose in VBA.

Step 1: Define a sub-procedure to store a macro.

Code:

Sub Trans_Ex2()

End Sub

VBA Transpose Example 2-1

Step 2: Decide the range where you want to transpose this data. It can be done using Sheets.Range.Value function. I would be saving the transposed array into cell D1 to I2.

Code:

Sub Trans_Ex2()

Sheets("Example #2").Range("D1:I2").Value =

End Sub

VBA Transpose Example 2-2

Step 3: Use WorksheetFunction.Transpose to be able to assign an array A1:B6 to transpose function.

Code:

Sub Trans_Ex2()

Sheets("Example #2").Range("D1:I2").Value = WorksheetFunction.Transpose(

End Sub

VBA Transpose Example 2-3

Step 4: We need to specify the argument for Transpose function. We wanted to transpose a range of array A1:B6. Thus, use Range(“A1:B6”) as an argument.

Code:

Sub Trans_Ex2()

Sheets("Example #2").Range("D1:I2").Value = WorksheetFunction.Transpose(Range("A1:B6"))

End Sub

VBA Transpose Example 2-4

Step 5: Hit F5 or Run button to run this code and see the output.

Result of Example 2-5

Here, range of array A1:B6 is transposed and stored into a range of array D1:I2 with the help of VBA Transpose function in excel.

Example #3 – VBA Transpose of an array with Paste Special Method

We can also transpose the array and paste as special as well do in Excel using Alt + E + S. We can use different operations under this paste special method.

Let us consider the same data as we have used in the previous example.

VBA Transpose Example 3

Follow the below steps to use Transpose in VBA.

Step 1: Define a sub-procedure to store the macro.

Code:

Sub Trans_Ex3()

End Sub

VBA Transpose Example 3-2

Step 2: Define two new variables, one which can hold the data source array (sourceRng) and others which can hold the output array range (targetRng).

Code:

Sub Trans_Ex3()

Dim sourceRng As Excel.Range
Dim taretRng As Excel.Range

End Sub

VBA Transpose Example 3-3

Please note that the type of these variables defined as (Excel.Range). Because we wanted to transpose the data which is an array range.

Step 3: Set the source range as A1:B6 (The data we wanted to take the transpose of ) using Sheets.Range function.

Code:

Sub Trans_Ex3()

Dim sourceRng As Excel.Range
Dim taretRng As Excel.Range
Set sourceRng = Sheets("Example #3").Range("A1:B6")

End Sub

VBA Transpose Example 3-4

Step 4: Set the target/destination range as D1:I2 (The range of cells where the output will be stored) using Sheets.Range function.

Code:

Sub Trans_Ex3()

Dim sourceRng As Excel.Range
Dim taretRng As Excel.Range
Set sourceRng = Sheets("Example #3").Range("A1:B6")
Set targetRng = Sheets("Example #3").Range("D1:I2")

End Sub

Example 3-5

Step 5: Now, use the command Copy to copy the source data range from your worksheet.

Code:

Sub Trans_Ex3()

Dim sourceRng As Excel.Range
Dim taretRng As Excel.Range
Set sourceRng = Sheets("Example #3").Range("A1:B6")
Set targetRng = Sheets("Example #3").Range("D1:I2")
sourceRng.Copy

End Sub

Example 3-6

Step 6: We are going to use PasteSpecial function on variable targetRng to save the transposed output under the target range (D1:I2) in your worksheet.

Code:

Sub Trans_Ex3()

Dim sourceRng As Excel.Range
Dim taretRng As Excel.Range
Set sourceRng = Sheets("Example #3").Range("A1:B6")
Set targetRng = Sheets("Example #3").Range("D1:I2")
sourceRng.Copy
targetRng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End Sub

Example 3-7
  • Paste: Allows to paste values in a different format (like Paste as Values, as Formulae, As Format, etc.). It is similar as to that of Excel (We do Alt + E + S to have different paste special options). In our example, we have set it to paste as values.
  • Operation: There are different operations which can be performed like addition, subtraction, multiplication, division (in the same way we can do in Excel).
  • SkipBlanks: If set True, this command allows to skip the blanks if any from your data while doing different paste special operations. We set it to False, means we wanted not to skip the blanks.
  • Transpose: If set True, it allows to take the transpose of an array of data.

Step 7: Run this code by hitting F5 or Run button and see the output.

Result of Example 3-8

Things to Remember

  • While working on a one-dimensional array, it should always be horizontal (One row, multiple columns) in order to apply the transpose method.
  • It is mandatory to find out the number of rows and number of columns while using VBA Transpose in Excel. If we have 3 rows and 5 columns, after transpose there would be 5 rows with three columns.
  • Normally, Transpose method does not include formatting of the source data. If you want to apply the same format as that of source data, you either manually have to set it or using paste special option XlPasteFormat you will be able to hold the format of source data to the target data.
  • The number of elements Transpose function can take in an array cannot exceed 5461.
  • An array cannot contain any item/string having length more than 255. If included, it will cause errors such as 13, Type Mismatch, 5, Invalid procedure call or argument, 1004, application defined or object defined error.
  • The source array cannot contain any null value (such as “Null”, “#N/A”).

Recommended Articles

This has been a guide to VBA Transpose. Here we discussed how to use Excel VBA Transpose along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA String
  2. VBA XML
  3. VBA Case
  4. VBA Month
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more