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.