[Fixed] AutoFit Row Height Not Working for Merged Cells in Excel

In general, the AutoFit Row Height and the AutoFit Column Width command automatically fit all the cell contents in an Excel worksheet. But if your Excel worksheet contains any merged cell, then the AutoFit Row Height and the AutoFit Column Width will not work there. You have to manually fit the cell contents in that case. If you have to deal with a lot of merged cells, manually fitting each of the cell’s contents can be quite daunting. But in this article, you will get solutions for dealing with AutoFit Row Height now working for merged cells in Excel.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


2 Solutions: AutoFit Row Height Not Working for Merged Cells in Excel

1. Use an Updated Version of MS Excel (2010, 2013, 2016, 2019, 2021, 365 Versions)

To demonstrate the AutoFit Row Height command not working against the merged cells in Excel, I am going to use the following dataset of Product Price List.

In the following dataset, the Product column has several contents into the merged cells.

Now, to apply the AutoFit Row Height command,

❶ Select the entire dataset.

❷ Go to the Home ribbon.

❸ Under the Cells group, you will find the Format option. Click on it.

❹ Then from the drop-down menu, choose AutoFit Row Height.

AutoFit Row Height Not Working for Merged Cells in Excel

After applying the AutoFit Row Height command, you will see that all the cells have fitted except the merged cells.

📓 Note

The AutoFit Row Height command doesn’t work against the merged cells in Excel 2003 and 2007. However, this command perfectly works in Microsoft Office 365, and other updated versions.

So we recommend using updated versions of MS Excel to avoid such issues.

Read More: How to Autofit in Excel (7 Easy Ways)


Similar Readings:


2. Visual Basic Script to Solve AutoFit Row Height Not Working against the Merged Cells in Excel

In the following dataset, under the Product column, the cells are all merged. Applying the AutoFit Row Height command doesn’t work here. Thus we need a VBA script to handle this problem.

In this regard,

❶ Press ALT + F11 to open the VBA editor.

❷ Then go to Insert > Module. This will create a new module.

❸ After that copy the following VBA code:

Option Explicit
Public Sub FitTheMergedCells()
Call MergedCellsAutoFit(Range("B5:C6"))
Call MergedCellsAutoFit(Range("B7:C8"))
Call MergedCellsAutoFit(Range("B9:C11"))
Call MergedCellsAutoFit(Range("B12:C12"))
End Sub
Public Sub MergedCellsAutoFit(gg As Range)
Dim aa As Integer
Dim bb As Integer
Dim cc As Single
Dim dd As Single
Dim ee As Single
Dim ff As Single
With Sheets("Sheet1")
cc = 0
For bb = 1 To gg.Columns.Count
cc = cc + .Cells(1, gg.Column + bb - 1).ColumnWidth
Next bb
cc = .Cells(1, gg.Column).ColumnWidth + .Cells(1, gg.Column + 1).ColumnWidth
gg.MergeCells = False
ee = Len(.Cells(gg.Row, gg.Column).Value)
dd = .Range("ZZ1").ColumnWidth
.Range("ZZ1") = Left(.Cells(gg.Row, gg.Column).Value, ee)
.Range("ZZ1").WrapText = True
.Columns("ZZ").ColumnWidth = cc
.Rows("1").EntireRow.AutoFit
ff = .Rows("1").RowHeight / gg.Rows.Count
.Rows(CStr(gg.Row) & ":" & CStr(gg.Row + gg.Rows.Count - 1)).RowHeight = ff
gg.MergeCells = True
gg.WrapText = True
.Range("ZZ1").ClearContents
.Range("ZZ1").ColumnWidth = dd
End With
End Sub

❹ Now come back to the VBA editor and press CTRL + V to paste the code.

Visual Basic Script to Solve AutoFit Row Height Not Working Against the Merged Cells in Excel

❺ Call the functions to fix each of the merged cells. Each time insert the cell range of the merged cells like the followings:

Call MergedCellsAutoFit(Range("B5:C6"))
Call MergedCellsAutoFit(Range("B7:C8"))
Call MergedCellsAutoFit(Range("B9:C11"))
Call MergedCellsAutoFit(Range("B12:C12"))

❻ Insert your worksheet name here:

With Sheets("Sheet1")

❼  Press CTRL + S to save the code.

❽  To run the code, press the F5 key or hit the Run Sub button.

Then the Macros dialog box will appear.

❾  Hit the Run button to execute the code.

After following all the steps, you will see the merged cells are not auto fitted with their corresponding cell contents instantly as in the picture below:

[Solve] AutoFit Row Height Not Working Against the Merged Cells in Excel

Read More: How to Use AutoFit Shortcut in Excel (3 Methods)


Things to Remember

  • Press ALT + F11 to open the VBA editor.
  • To run the VBA code, press the F5 button.

Conclusion

To sum up, we have discussed solutions to tackle the AutoFit Row Height not working against the merged cells in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

4 Comments
  1. I use Microsoft Office 2019 and autofit row height preferences is not working for merged cells. There is any suggestion on this issue?

    • Hello, GOKTUG! All of our contents were prepared based on Excel 365 version only. So, I recommend using the Excel 365 version instead of 2019. Hopefully it should fix your issues!

  2. Hi All,

    How can I apply this as a loop to all worksheets in a file (100 tabs). The “With Sheets(“Sheet1″)” in this case will be applicable to all sheet names. Thank you in advance.

    • Reply
      Naimul Hasan Arif Sep 4, 2022 at 1:01 PM

      Use of “for loop” function is a very simple approach for this purpose. You can use the following code to merged the defined cells to 100 tabs. Based on your sheets, you just need to change the value of “i” in the code.

      Option Explicit
      Public Sub FitTheMergedCells()
      Call MergedCellsAutoFit(Range(“B5:C6”))
      Call MergedCellsAutoFit(Range(“B7:C8”))
      Call MergedCellsAutoFit(Range(“B9:C11”))
      Call MergedCellsAutoFit(Range(“B12:C12”))
      End Sub
      Public Sub MergedCellsAutoFit(gg As Range)
      Dim aa As Integer
      Dim bb As Integer
      Dim cc As Single
      Dim dd As Single
      Dim ee As Single
      Dim ff As Single
      Dim i As Integer
      For i = 1 To 100
      With Sheets(“Sheet” & i)
      cc = 0
      For bb = 1 To gg.Columns.Count
      cc = cc + .Cells(1, gg.Column + bb – 1).ColumnWidth
      Next bb
      cc = .Cells(1, gg.Column).ColumnWidth + .Cells(1, gg.Column + 1).ColumnWidth
      gg.MergeCells = False
      ee = Len(.Cells(gg.Row, gg.Column).Value)
      dd = .Range(“ZZ1”).ColumnWidth
      .Range(“ZZ1”) = Left(.Cells(gg.Row, gg.Column).Value, ee)
      .Range(“ZZ1”).WrapText = True
      .Columns(“ZZ”).ColumnWidth = cc
      .Rows(“1”).EntireRow.AutoFit
      ff = .Rows(“1”).RowHeight / gg.Rows.Count
      .Rows(CStr(gg.Row) & “:” & CStr(gg.Row + gg.Rows.Count – 1)).RowHeight = ff
      gg.MergeCells = True
      gg.WrapText = True
      .Range(“ZZ1”).ClearContents
      .Range(“ZZ1”).ColumnWidth = dd
      End With
      Next i
      End Sub

Leave a reply

ExcelDemy
Logo