How to auto fit row height of merged cells in Excel?
In Excel, adjusting row heights quickly and accurately is crucial for presenting your data clearly. Normally, you can use the AutoFit Row Height feature to automatically resize a row's height to fit its cell contents. However, when working with merged cells, you might notice that AutoFit Row Height does not respond as expected. That is, Excel's AutoFit function completely ignores merged cells, so you can’t automatically resize the row height to accommodate the content in these cases. As a result, you typically have to manually adjust the row height for each merged cell to ensure all the content is visible, which can be tedious and error-prone—especially when you have many merged cells.
This article introduces several practical methods to help auto fit the row height of merged cells effectively, saving you time and helping maintain a clean, readable worksheet:
Auto fit row height of merged cells with VBA code
Use Wrap Text and manual adjustment
Auto fit row height of merged cells with VBA code
Suppose your worksheet contains several merged cells with varying amounts of content, as shown in the screenshot below. If you want all your data to be fully visible without manually adjusting every row, you can use a VBA code solution to automatically fit the row height to the contents of each merged cell at once. This method is especially helpful when you have many merged ranges throughout your sheet, or when merged cell contents are frequently updated and you want to streamline the adjustment process.
Before running any VBA, remember to save your workbook as a macro-enabled file to prevent loss of unsaved work. VBA is a secure method provided by Excel, but ensure that macros are enabled and your security settings permit macro execution on your system.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, then copy and paste the following code into the Module Window:
VBA code: Auto fit row height of multiple merged cellsOption Explicit
Public Sub AutoFitAll()
Call AutoFitMergedCells(Range("a1:b2"))
Call AutoFitMergedCells(Range("c4:d6"))
Call AutoFitMergedCells(Range("e1:e3"))
End Sub
Public Sub AutoFitMergedCells(oRange As Range)
Dim tHeight As Integer
Dim iPtr As Integer
Dim oldWidth As Single
Dim oldZZWidth As Single
Dim newWidth As Single
Dim newHeight As Single
With Sheets("Sheet4")
oldWidth = 0
For iPtr = 1 To oRange.Columns.Count
oldWidth = oldWidth + .Cells(1, oRange.Column + iPtr - 1).ColumnWidth
Next iPtr
oldWidth = .Cells(1, oRange.Column).ColumnWidth + .Cells(1, oRange.Column + 1).ColumnWidth
oRange.MergeCells = False
newWidth = Len(.Cells(oRange.Row, oRange.Column).Value)
oldZZWidth = .Range("ZZ1").ColumnWidth
.Range("ZZ1") = Left(.Cells(oRange.Row, oRange.Column).Value, newWidth)
.Range("ZZ1").WrapText = True
.Columns("ZZ").ColumnWidth = oldWidth
.Rows("1").EntireRow.AutoFit
newHeight = .Rows("1").RowHeight / oRange.Rows.Count
.Rows(CStr(oRange.Row) & ":" & CStr(oRange.Row + oRange.Rows.Count - 1)).RowHeight = newHeight
oRange.MergeCells = True
oRange.WrapText = True
.Range("ZZ1").ClearContents
.Range("ZZ1").ColumnWidth = oldZZWidth
End With
End Sub
Notes:
(1.) If you need to adjust additional merged ranges, simply copy the line Call AutoFitMergedCells(Range("a1:b2")) as many times as required, and update the address for each merged range you wish to auto fit.
(2.) Remember to change Sheet4 in the script to match the worksheet name where your merged cells are located. If you're unsure of your worksheet name, check the sheet tabs at the bottom of your Excel window.
(3.) When running macros, ensure that no cells containing critical or sensitive data are inadvertently included in the selected ranges, to avoid unexpected formatting changes.
3. Press F5 to run this code. After execution, all specified merged cells will automatically adjust their row height to fully display the cell contents, as illustrated below:
This VBA solution provides a convenient, automated way to handle merged cell sizing. However, it requires access to the Developer tab and permission to use macros—keep this in mind if you’re collaborating in environments where macros are disabled or restricted.
Use Wrap Text and manual adjustment
For many scenarios, you can combine the Wrap Text feature with a quick manual row height adjustment to ensure all content is clearly visible without relying on VBA or complex tools.
Step 1: Enable Wrap Text
- Select the merged cell(s) you want to format.
- Go to the Home tab and click Wrap Text in the Alignment group.
This will allow the content to automatically wrap to new lines within the merged area.
Step 2: Check display and manually adjust row height if needed
If your text isn't very long, it may now fit perfectly inside the merged cell.
If the content is longer and still not fully visible, manually drag the row boundary below the merged cell to increase the row height until all content is displayed.
Related article:
How to auto fit column width in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in