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.

Example

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.

Conclusion

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

Pradeep Kumar

Updated on: 10-Jan-2023

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started