You’ve most likely heard this warning — “Avoid merged cells in your Excel worksheets!”, and that is excellent advice. Merged cells can cause problems, especially when they’re in a table that you’ll be sorting and filtering. You’ll run into more problems if you try to autofit merged cell row height.
Forced to Merge
Occasionally though, you might have no choice but to use one or more merged cells on a worksheet. As long as you avoid merging table cells, and proceed with caution, things might be okay.
In the example shown below, there is an order form, and space for a note about the order. If the note will always be short, there’s no need to merge the cells – just let the text flow across the columns.
However, if the notes will be two or more lines, you’ll need to merge the cells, and turn on Wrap Text. Adjusting the column width would affect the product list that starts in row 12, so that’s not an option.
Merged Cell Row Height
Usually, if you add more text to a single cell, and Wrap Text is turned on, the row height automatically adjusts, to fit the text.
When the cells are merged in row 10, the row height has to be manually adjusted when the text changes. That works well, as long as you remember to do it, but it can be a nuisance, if the text changes frequently.
And if you forget to adjust the row height, you might print the order form, while key instructions are hidden.
AutoFit Merged Cell Row Height
To fix the worksheet, so the merged cells adjust automatically, you can add event code to the worksheet.
[Update: The original code is below, and there are several modified versions of the code in the comments. There is also an updated version of Smallman’s code in this December 2015 blog post.]
The merged cells are named OrderNote, and that name will be referenced in the event code.
Code to AutoFit Merged Cell Row Height
We want the row height to adjust if the OrderNote range is changed, so we’ll add code to the Worksheet_Change event.
The code that I use is based on an old Excel newsgroup example, that was posted by Excel MVP, Jim Rech.
Note: As Jeff Weir pointed out in the comments below, this code will wipe out the Undo stack, so you won’t be able to undo any steps you’ve previously taken. So, instead of using the Worksheet_Change event, you could use the workbook’s BeforePrint event, to reduce the Undo problem.
- Right-click on the sheet tab, and paste the following code on the worksheet module. Note: Only one Worksheet_Change event is allowed in each worksheet module.
- Change the range name from “OrderNote”, to the named range on your worksheet.
- If your worksheet is protected, you can add code to unprotect and protect the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MergeWidth As Single
Dim cM As Range
Dim AutoFitRng As Range
Dim CWidth As Double
Dim NewRowHt As Double
Dim str01 As String
str01 = "OrderNote"
If Not Intersect(Target, Range(str01)) Is Nothing Then
Application.ScreenUpdating = False
On Error Resume Next
Set AutoFitRng = Range(Range(str01).MergeArea.Address)
With AutoFitRng
.MergeCells = False
CWidth = .Cells(1).ColumnWidth
MergeWidth = 0
For Each cM In AutoFitRng
cM.WrapText = True
MergeWidth = cM.ColumnWidth + MergeWidth
Next
'small adjustment to temporary width
MergeWidth = MergeWidth + AutoFitRng.Cells.Count * 0.66
.Cells(1).ColumnWidth = MergeWidth
.EntireRow.AutoFit
NewRowHt = .RowHeight
.Cells(1).ColumnWidth = CWidth
.MergeCells = True
.RowHeight = NewRowHt
End With
Application.ScreenUpdating = True
End If
End Sub
How It Works
The event code checks to see if the changed cell is in the OrderNote range. If it is, the code runs, and does the following:
- Unmerge the cells
- Get the width of the first column in the OrderNote range
- Get the total width for all columns in the OrderNote range
- Add a little extra to the calculated width
- Set the first column to the calculated total width
- Autofit the row, based on the note next in the first column
- Get the new row height
- Change the first column to its original width
- Merge the cells
- Set the row height to the new height
Screen updating is turned off while the code runs, and it all happens in the blink of an eye.
Test the Event Code
To test the code, make a change to the text in the named merged cells, then press Enter. The row height should adjust automatically.
Is this code, to AutoFit merged cell row height, something that you’ll use in your workbooks? Please let me know in the comments.
__________________
Hi
I have been on this website countless times over the years. Deb should be congratulated for creating such a great forum for knowledge sharing. I had a friend ask me to crack this one for his company and I ended up on this page. I noticed the last three posts are looking for a solution for multiple merged cells. I altered the variables a bit and added the function for multiple cell ranges, which can be changed to suit your needs. Here is my take on the problem. It is to go in a regular module so you can run it at will.
I could have included a working copy of the procedure if the site offered the ability to upload files.
Take care
Smallman
Option Explicit Option Base 1 Sub FixMerged() Dim mw As Single Dim cM As Range Dim rng As Range Dim cw As Double Dim rwht As Double Dim ar As Variant Dim i As Integer Application.ScreenUpdating = False 'Cell Ranges below, change to suit. ar = Array("C64", "C67", "C69", "C71", "C73", "C75") For i = 1 To UBound(ar) On Error Resume Next Set rng = Range(Range(ar(i)).MergeArea.Address) With rng .MergeCells = False cw = .Cells(1).ColumnWidth mw = 0 For Each cM In rng cM.WrapText = True mw = cM.ColumnWidth + mw Next mw = mw + rng.Cells.Count * 0.66 .Cells(1).ColumnWidth = mw .EntireRow.AutoFit rwht = .RowHeight .Cells(1).ColumnWidth = cw .MergeCells = True .RowHeight = rwht End With Next i Application.ScreenUpdating = True End SubHi Smallman,
You’re code is working perfectly for me! thanks! I have one more small request… How can I add a parameter somewhere that keeps the merged cells unlocked? My sheet is password protected and once I run this code, the merged cells become locked and I can no longer enter any data.
Thanks in advance!
Suzi
first off i like to thank you finding this page has helped so much.
but i do have some issues after impelmenting the code for the range of cells, the code only works by manually running the macros did i miss a step to have the code automatically adjust the size of the comment boxes once the user has left each cell in the range. Another issue is that the code shrinks the cells that have not yet have text added to them. is there a way to have the work book keep the formatting of all cells in the range if no text is entered.
thanks
Peter
You are a life saver!!! Thanks so much for providing this code.
@Jennifer, you’re welcome! Thanks for letting me know that it helped you.
@Suzi: select the merged cells, then in the format cells dialog (which you can launch by pressing CTRL F1), select the Protection tab, then uncheck the Locked tickbox.
Hi Suzi
Thanks, pleased it works for you. I expect that you want to do this on the fly within the procedure you are currently running not manually. With the example above you could use something like the following within your procedure to unlock the cells in question. First unlock the sheet, then the cells, then lock the sheet up again. Hope it helps.
Take care
Smallman
Option Explicit Sub UnLockRng() Dim rEntry As Range Sheet1.Unprotect 'Unprtotect sheet Set rEntry = Range("C64, C67, C69, C71, C73, C75") rEntry.Locked = False Sheet1.Protect 'Protect sheet End SubHi there. I am very, very novice at coding, and have no idea where to insert Smallman’s Option Explicit code (detailed above) into the main chunk of code that Jeff Weir laid out in his 2012 article (which works fine except it locks the cell after resizing, and I don’t want the cell locked). I am still running Excel 2010, and my spreadsheet has no password. I just protect, and unprotect, the sheet with the icon as it is a multi-user spreadsheet.
I tried Jeff’s updated code at https://contexturesblog.com/archives/2015/12/03/autofit-merged-cells-row-height-update-20151203/ but I can’t get that code to work at all, so can only assume that this only works in later versions of Excel. I also tried the following code, but I couldn’t get that to work either:
Private Sub Workbook_Open()
Dim wSheetName As Worksheet
For Each wSheetName In Worksheets
wSheetName.Protect Password:=”Secret”, UserInterFaceOnly:=True
Next wSheetname
End Sub
Can anyone advise where the Option Explicit code should sit?
@Smallman: That code’s not needed if the cells are manually set to .Locked = false, as I suggest above.
Either way, both methods work. What you suggest does not achieve a different result. Run the code once, or do it manually. Choices, isn’t that the point? Thanks for providing your input.
Take care
Smallman
Yes, both work. But after your code has been run once, it’s superfluous.