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.

How to transpose Values in a column to a row using VBA

          Today I'm going to create very simple macro to transpose values in a column to a Row. Assume we have names of the countries of the world in column A. So I have 257 country names in this column A. What I need to do is transpose these names to 1st row.  Here is a image of my current worksheet.

































So we are going to put all these country names to row 1 using a simple macro.

First we need to open the VBA editor. Let's use shortcut keys for that. Press Alt+F11

You will get a window similar to below.

































Click on Insert == > Module

































Then Copy paste below code to coding area.

Sub TransposeData()

Dim WS As Worksheet

Set WS = ActiveSheet

columnNumber = 1

For i = 1 To 257

    Cells(1, columnNumber) = Cells(i, 1).Text
   
    'Increment column number by 1
    columnNumber = columnNumber + 1

Next i

MsgBox "Completed"

End Sub

Then click on any line between two lines Sub TransposeData( ) and End Sub to select the macro.




Then click on the run button.




You will get the following result.




Then you can delete the values in column A except cell A1.

So now let's consider about the code



Dim WS As Worksheet

This defines the worksheet variable

Set WS = ActiveSheet

So we have set active sheet to the variable WS

columnNumber = 1

This defines where we need to start entering the values. So here we order to insert from column 1 to column 257. We can change the number depending on where we need to enter our first value.

For i = 1 To 257

Next i

We used for next loop to go trough all the rows of column A

Cells(1, columnNumber) = Cells(i, 1).Text

This takes value from current cell of column A and assign it to relevant column of row 1.

columnNumber = columnNumber + 1

This line increment the columnNumber by 1. So next value of column A will be entered in next column of row 1.

Contact Form

Name

Email *

Message *

Popular Posts