Insert and Merge Rows Below Maximum-Height Excel Rows with VBA
Question details
The user needs a VBA macro to add space for wrapped text in Excel rows that have reached the maximum height.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Scan a worksheet region, insert a row below each maximum-height row, and merge corresponding cells to extend the display area.
- Observed behavior
- The macro inserts and merges cells, but the inserted row remains at its default height, leaving wrapped text potentially hidden.
Identify the column containing the text and select a cell inside the data region. Check for existing merged cells, since the example skips already-merged target cells and inserting entire rows can affect other worksheet content.
Insert rows bottom-up and set their height explicitly
Use this approach when text is in one column and the target cells are not already merged.
The example scans the active cell's current region from bottom to top, so inserted rows are not processed again. It inserts an entire worksheet row for each qualifying original row, then merges the cell in column A with the blank cell below it.
The added height is a starting value, not an exact text measurement. AutoFit is unreliable for merged cells, so inspect the results and adjust the height as needed.
In Excel, open the Developer tab and select Visual Basic. In the VBA editor, choose Insert > Module.
Paste the following code into the module. Change textCol to the column number containing your text, and change addedHeight to a suitable height in points: Sub ExtendMaxHeightRows() Dim ws As Worksheet Dim region As Range Dim r As Long, firstRow As Long, lastRow As Long Dim textCol As Long Dim addedHeight As Double Set ws = ActiveSheet Set region = ActiveCell.CurrentRegion firstRow = region.Row lastRow = region.Row + region.Rows.Count - 1 textCol = 1 ' Column A addedHeight = 120 For r = lastRow To firstRow Step -1 If ws.Rows(r).RowHeight >= 409 Then If Not ws.Cells(r, textCol).MergeCells Then ws.Rows(r + 1).Insert Shift:=xlDown ws.Rows(r + 1).RowHeight = addedHeight With ws.Range(ws.Cells(r, textCol), ws.Cells(r + 1, textCol)) .Merge .WrapText = True .VerticalAlignment = xlTop End With End If End If Next r End Sub
Select a cell in the data region, then use Developer > Macros to run ExtendMaxHeightRows. Inspect the merged cells and adjust an inserted row through Home > Format > Row Height if text is still clipped.

Adjust Wrapped Spreadsheet Text with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and adjust wrapping or row height in WPS Spreadsheets for an easy transition, although it does not fix Excel's VBA or merged-cell AutoFit behavior.

Frequently Asked Questions
Why doesn't AutoFit show all the text in a merged cell?
Excel's AutoFit does not reliably calculate row height for merged cells. Select the affected row and use Home > Format > Row Height to set a height explicitly.
Why should the macro scan rows from bottom to top?
Inserting a row shifts rows below it. A bottom-up loop processes each original row before an insertion above it can change its position, preventing newly inserted rows from being processed again.
How do I change which column the macro merges?
In the VBA module, change the textCol value: use 1 for column A, 2 for column B, and so on. The example merges only the specified column.
What if the text is still clipped after the macro runs?
Increase addedHeight in the VBA code or select the inserted row and choose Home > Format > Row Height. If the combined height is still insufficient, consider using unmerged cells or another layout for the text.




