VBA & Macro Problems

Insert and Merge Rows Below Maximum-Height Excel Rows with VBA

Amos GikundaAmos Gikunda Nov 14, 2026 869 views

Question details

The user needs a VBA macro to add space for wrapped text in Excel rows that have reached the maximum height.

Insert and Merge Rows Below Maximum-Height Excel Rows with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic. In the VBA editor, choose Insert > Module.

2
Add the macro

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

3
Run and inspect the result

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.

Insert rows bottom-up and set their height explicitly
Limits of the example: The macro merges only one specified column and skips target cells that are already merged. Entire-row insertion shifts other worksheet content, and existing merged ranges can interfere. Excel limits each row to 409.5 points, so one additional row may still be insufficient for very long text.
Free Microsoft Office alternative

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.

Adjust row height and text wrapping in spreadsheetsCompatible with Microsoft Excel XLS and XLSX filesFamiliar interface for users moving from Microsoft OfficeFree and lightweight office suite
microsoft office alternative - wps office

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.