How to transpose columns to rows and insert new rows to fill down data in Excel?
In this article, I introduce a VBA code to transpose columns to rows and insert new rows to fill down data as below screenshot shown, if you are interested in this operation, please go to below for more details.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
There is no direct way to transpose a range and insert rows to fill down same data, but here I can introduce a VBA code to quickly solve this task.
1. Press Alt + F11 keys to open Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste below VBA code to the Module window. See screenshot:
VBA: Transpose and insert rows.
Sub TransposeInsertRows() 'UpdatebyExtendoffice20161125 Dim xRg As Range Dim i As Long, j As Long, k As Long Dim x As Long, y As Long Set xRg = Application.InputBox _ (Prompt:="Range Selection...", _ Title:="Kutools For Excel", Type:=8) Application.ScreenUpdating = False x = xRg(1, 1).Column + 2 y = xRg(1, xRg.Columns.Count).Column For i = xRg(xRg.Rows.Count, 1).Row To xRg(1, 1).Row Step -1 If Cells(i, x) <> "" And Cells(i, x + 1) <> "" Then k = Cells(i, x - 2).End(xlToRight).Column If k > y Then k = y For j = k To x + 1 Step -1 Cells(i + 1, 1).EntireRow.Insert With Cells(i + 1, x - 2) .Value = .Offset(-1, 0) .Offset(0, 1) = .Offset(-1, 1) .Offset(0, 2) = Cells(i, j) End With Cells(i, j).ClearContents Next j End If Next i Application.ScreenUpdating = True End Sub
3. Press F5 key to run the code, and then select the data range you want to transpose and insert rows into the popping dialog. See screenshot:
4. Click OK. And the selected range has been converted as below screenshot shown.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 years agohow would i change the VBA code so that it will keep more of the columns the same? for example instead of the first two columns staying the same it will be the first 4?
To post as a guest, your comment is unpublished.· 1 years agoIf my cross table's informations come from formulas, those formulas don't always follow when the macro is applied. How could I fix this to make sure that the formulas are kept in the cells?
To post as a guest, your comment is unpublished.