VBA & Macro Problems

Split Line-Break Data into Rows Across Excel Columns with VBA

Huda QurayshiHuda Qurayshi Nov 18, 2026 869 views

Question details

Split line-break-separated values in multiple Excel columns into separate rows and repeat the related first-column value.

Split Line-Break Data into Rows Across Excel Columns with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has a related value in the first column and multiline data in other columns. Each line should occupy a separate row.
Observed behavior
The desired result is one row per line position, with corresponding values aligned across columns and the first-column value repeated.
Before you start

Use desktop Excel with a header row, a value in column A for every data row, and headers extending through the last column to process. Ensure the data area has no merged cells or error values; the macro inserts entire worksheet rows and replaces processed cells with text values.

Solution 1Recommended

Use a VBA macro to split and align multiline cells

Process rows from bottom to top so inserted rows do not disrupt data that has not yet been processed.

The macro aligns lines by position across columns B through the last header column. If a cell has fewer lines than another cell in the same source row, its remaining output cells are left blank.

1
Open the VBA editor

In desktop Excel, open the worksheet you want to process, then select Developer > Visual Basic. In the VBA editor, select Insert > Module.

2
Add the macro

Paste this code into the module: Sub SplitMultilineRows() Dim ws As Worksheet Dim lastRow As Long, lastCol As Long Dim r As Long, c As Long, i As Long, maxParts As Long Dim textValue As String, parts As Variant, firstValue As Variant Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column If lastRow < 2 Or lastCol < 2 Then Exit Sub For r = lastRow To 2 Step -1 maxParts = 1 firstValue = ws.Cells(r, 1).Value2 For c = 2 To lastCol textValue = CStr(ws.Cells(r, c).Value2) textValue = Replace(Replace(textValue, vbCrLf, vbLf), vbCr, vbLf) parts = Split(textValue, vbLf) If UBound(parts) + 1 > maxParts Then maxParts = UBound(parts) + 1 Next c If maxParts > 1 Then ws.Rows(r + 1).Resize(maxParts - 1).Insert End If For i = 1 To maxParts - 1 ws.Cells(r + i, 1).Value2 = firstValue Next i For c = 2 To lastCol textValue = CStr(ws.Cells(r, c).Value2) textValue = Replace(Replace(textValue, vbCrLf, vbLf), vbCr, vbLf) parts = Split(textValue, vbLf) For i = 0 To maxParts - 1 If i <= UBound(parts) Then ws.Cells(r + i, c).NumberFormat = "@" ws.Cells(r + i, c).Value2 = parts(i) Else ws.Cells(r + i, c).ClearContents End If Next i Next c Next r End Sub

3
Run the macro

Return to Excel and activate the intended worksheet. Select Developer > Macros, choose SplitMultilineRows, and select Run.

4
Check and save the result

Review the inserted rows and aligned values. To keep the macro, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).

Use a VBA macro to split and align multiline cells
Check the worksheet layout: Inserted rows affect the entire worksheet, including content outside the processed columns. Blank lines, including trailing line breaks, count as line positions.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work between office suites. It does not fix Excel VBA issues, and macro support can vary by WPS version and platform.

Review and edit multiline spreadsheet data in WPS Spreadsheets.Open and save common Microsoft Excel formats, including XLSX.Use a familiar interface for everyday spreadsheet tasks.Move existing spreadsheet files into a lightweight office suite.
QA img-9

Frequently Asked Questions

What happens when columns have different numbers of lines?

The macro creates enough rows for the cell with the most lines. Columns with fewer lines have blank cells in the remaining output rows.

Why does the macro process rows from the bottom up?

Inserting rows below a source row can shift other rows. Starting with the last data row prevents those insertions from changing the positions of rows still to be processed.

Does the macro repeat values from columns other than column A?

No. It repeats only column A. Columns B through the last header column are split by line position, and missing positions are left blank.

Will the macro preserve formulas in the processed columns?

No. It reads cell values and writes the split results as text, so formulas in columns B onward are replaced. Review the worksheet before selecting Developer > Macros > Run.

How do I keep the macro in my workbook?

In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). An XLSX file cannot retain the VBA code.