In general, the AutoFit Row Height and the AutoFit Column Width commands 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.
How to Fix If AutoFit Row Height Is Not Working for Merged Cells in Excel: 2 Suitable Solutions
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 the Product Price List.
In the following dataset, the Product column has several contents in 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.
After applying the AutoFit Row Height command, you will see that all the cells have fitted except the merged cells.
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
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.
❺ Call the functions to fix each of the merged cells. Each time insert the cell range of the merged cells like the following:
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:
❼ 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:
Read More: How to Use AutoFit Shortcut in Excel
Things to Remember
- Press ALT + F11 to open the VBA editor.
- To run the VBA code, press the F5 button.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
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.
- How to Make Excel Cells With The Same Height and Width
- How to Make a Cell Bigger in Excel
- How to Make All Cells the Same Size in Excel
- How to Make Cells Independent in Excel
- How to Change Cell Size in Excel
- How to Reset Cell Size to Default in Excel
- How to Fix Cell Size in Excel
- How to Change Cell Size Without Changing Whole Column