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.
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write VBA codes that will create rows from column values in excel.
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
‘Name
.Cells(writeRow, 1).Value = data(row, 1)
‘Language
.Cells(writeRow, 2).Value = data(row, col)
writeRow = writeRow + 1
If col = maxCols Then Exit Do ‘Exit clause
col = col + 1
Loop
If row = maxRows Then Exit Do ‘exit cluase
row = row + 1
Loop
End With
End Sub
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.
