Convert Excel Columns into Rows with VBA Without Overwriting Headers
Question details
Convert a rectangular range into individual rows and retain the corresponding row-header value.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to process selected values from left to right and then top to bottom, placing each value in a new row below the source range.
- Observed behavior
- Each new row should contain one selected value and its original row header, without overwriting the source headers.
Arrange the row headers immediately to the left of the data range, and select only the data cells—not the headers. The macro inserts worksheet rows below the selection, shifting existing rows downward.
Use an Excel VBA macro to create rows with matching headers
Use this macro when each selected data cell needs its own row and its source row header is in the column immediately to the left.
The macro reads the selection before inserting rows, then writes headers to the column on the left and values to the first selected column. It includes blank cells and copies values, not formulas.
The original range remains in place. Existing worksheet rows below it move down, and insertion can fail if Excel cannot shift the existing content.
In Excel, open the workbook and press Alt+F11. In the VBA editor, choose Insert > Module.
Paste this code into the module: Sub ConvertSelectionToRows() Dim src As Range, ws As Worksheet Dim vals() As Variant, headers() As Variant Dim r As Long, c As Long, n As Long Dim firstRow As Long, lastRow As Long, outRow As Long If TypeName(Selection) <> "Range" Then MsgBox "Select a rectangular range of data cells." Exit Sub End If Set src = Selection Set ws = src.Worksheet If src.Areas.Count <> 1 Or src.Column = 1 Then MsgBox "Select one contiguous range with headers in the column to its left." Exit Sub End If firstRow = src.Row + src.Rows.Count If firstRow > ws.Rows.Count Then MsgBox "There is no room below the selection." Exit Sub End If If src.CountLarge > ws.Rows.Count - firstRow + 1 Then MsgBox "There are not enough worksheet rows for the output." Exit Sub End If n = CLng(src.CountLarge) lastRow = firstRow + n - 1 ReDim vals(1 To src.Rows.Count, 1 To src.Columns.Count) ReDim headers(1 To src.Rows.Count) For r = 1 To src.Rows.Count headers(r) = src.Cells(r, 1).Offset(0, -1).Value For c = 1 To src.Columns.Count vals(r, c) = src.Cells(r, c).Value Next c Next r On Error GoTo InsertError ws.Rows(firstRow & ":" & lastRow).Insert Shift:=xlDown outRow = firstRow For r = 1 To src.Rows.Count For c = 1 To src.Columns.Count ws.Cells(outRow, src.Column - 1).Value = headers(r) ws.Cells(outRow, src.Column).Value = vals(r, c) outRow = outRow + 1 Next c Next r Exit Sub InsertError: MsgBox "The macro could not complete: " & Err.Description End Sub
Return to the worksheet and select one rectangular range with its row headers directly to the left. Open Developer > Macros, select ConvertSelectionToRows, and click Run.
Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) to keep the procedure in the workbook. Alternatively, store the procedure in PERSONAL.XLSB for use with other workbooks.

Edit Excel Workbooks with WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel file formats, making it easy to move everyday workbook editing to WPS. It does not fix Excel VBA issues, and macro support can vary by version.

Frequently Asked Questions
Which cells should I select before running the macro?
Select one contiguous rectangle of data cells on the worksheet. Place each row header immediately to the left of that rectangle, but do not include the headers in the selection.
Will the macro overwrite my original headers or data?
No. It inserts rows below the selection and writes the copied headers and values into those new rows. Existing rows below the selection shift downward.
Does the macro copy formulas and skip blank cells?
No. It writes cell values rather than formulas and creates an output row for every selected cell, including blanks.
Why can't Excel insert the new rows?
Excel may be unable to shift existing content down, or there may not be enough worksheet rows. Check the rows below the selection and reduce the selected range if needed.
How do I keep the macro available after closing Excel?
Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm), or place the procedure in PERSONAL.XLSB to make it available across workbooks.




