Create Rows From Column In Excel

In this post we will see how to create rows from column in excel, in other words we would like to create rows from column header by transposing the column values into rows.

Below is the data sample that shows we have data and we want to transpose the data in such a way that each column values are converted into rows.

Excel showing column data need to be converted into rows

Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to transform column values into rows

Step 2

Insert a module in which we will write VBA codes that will create rows from column values in excel.

Insert a module in the VB editor

Step 3

Now double click the newly created module to open that and paste the below code in the module.

Sub Create_Rows_From_Column()

    Dim maxRows As Double

    Dim maxCols As Integer

    Dim data As Variant

    maxRows = Cells(1, 1).End(xlDown).row

    maxCols = Cells(1, 1).End(xlToRight).Column

    data = Range(Cells(1, 1), Cells(maxRows, maxCols))

    Dim newSht As Worksheet

    Set newSht = Sheets.Add

    With newSht

        .Cells(1, 1).Value = “Name”

        .Cells(1, 2).Value = “Language”

        Dim writeRow As Double

        writeRow = 2

        Dim row As Double

        row = 2

        Dim col As Integer

        Do While True

            col = 2

            Do While True

                If data(row, col) = “” Then Exit Do ‘Skip Blanks


                .Cells(writeRow, 1).Value = data(row, 1)


                .Cells(writeRow, 2).Value = data(row, col)

                writeRow = writeRow + 1

                If col = maxCols Then Exit Do ‘Exit clause

                col = col + 1


            If row = maxRows Then Exit Do ‘exit cluase

            row = row + 1


    End With

End Sub

Code to change column into rows in excel

This program will create an new sheet and transpose the excel table into rows, you can customize the newly created column names by specifying column names in the program.

Now run the program by pressing F5 or by pressing the play button in VB editor.

We can see the result in the sheet that vba program has transposed the excel table.

Hope this helped.

Share The Knowledge

Random Posts