How to Auto-Fit Row Height of Merged Cells in Excel?

We can see that if we try to fit a large amount of data into one cell, the data will now be displayed if we move to another cell; the only way to solve this problem is to increase the row height of the cell. For only one cell, we can adjust the height of the row by using the command "Alt + Enter", but when we try to merge the cells of different heights, we can face the same problem; we can’t use the command. Read this tutorial to learn how you can solve this problem, i.e., how to autofit the row height of merged cells in Excel.

AutoFit Row Height of Merged Cells in Excel

Here we will first insert a VBA module, then run the code and select a range of cells to complete the task. Let us see a simple process to understand how we can automatically fit the row height of merged cells in Excel.

Step 1

Consider the following image of an Excel sheet with two cells of varying heights merged −

Now to adjust the row height, right-click on the sheet name and select view code to open the vba application, then click on insert and select module, then type "programme" into the text box as shown in the below image.


Option 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("Sheet1") 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

In the code, sheet1 is the name of the sheet, and we can adjust the range of cells based on our requirements.

Step 2

Now save the sheet as a macro-enabled template, then use the command F5 to run the code and complete our process successfully, as shown in the below image.


In this tutorial, we used a simple example to demonstrate how we can auto-fit the row height of merged cells in Excel.

Updated on: 10-Jan-2023

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started