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 All
I have finally got round to loading a file which incorporates some of the above. Seeing is believing and I hope the file helps show some of the theory above in a practical environment.
https://rapidshare.com/share/3C2165E05831B15C225E7BD16FF4EE23?bin=1
I have included 3 scenarios. One non Sequential Range, one sequential range and One OnChange Sheet which will update when the cells in Column C Change.
My file sharing website recently changed the way you share data so if the following does not work can someone, anyone, sing out and I will try again. If Rapidshare asks if you want to open an account just hit Cancel if you don’t want an account. GoodLuckski!!!
Take Care
Smallman
Smallman,
I read through all of these comments, and find that I have a slightly different issue. I have a set of 4 consecutive ranges (G20:g71,k20:k71,v20:v71,z20:z71), so when I tried to use the code that you share in this rapidshare example file, I found that I could only run it for one range at a time. If I tried to change “Set rng = Range(Range(“G” & i).MergeArea.Address)” to accommodate more than one range, then it merged them together. If I copied:
For i = 20 To Range(“g71”).Row
On Error Resume Next
Set rng = Range(Range(“G” & 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
and then changed the range from G to K, it only ran the second range.
I tried the other code you have above from 4/29/2013 but that doesn’t work at all (whereas this one does). I can confirm that wrap text is on, cells are merged, and the ranges are each named.
Any ideas about how I can pull this off?
Another issue I have is that I don’t want this to empty my undo cache every time it runs. I read somewhere else that the workbook beforeprint event only empties the undo cache at time of printing, but otherwise works like worksheet change. Do you have any wisdom for me there?
Thank you for any help you can give.
Patrick
Hi Smallman,
Thanks for posting a download link to the sample file. Unfortunately I receieve an “error on page” after the link goes to rapidshare. I share files via copy.com @ https://copy.com?r=oJPo1U
Thanks,
Noel
Hello everyone,
I see a lot of very bright minds on here, and I understand absolutely NOTHING to codes… Reading this post with all the suggestions that seem to work for others felt like reading an ancient foreign language to me… I was wondering if someone would be so kind to give a step by step almost of how to enter the code? I need to adjust row height automatically on merged cells, whose content are coming from a vlookup formula getting the data from another worksheet. The codes I have tried here make fully disappear the rows my cells are in, when they do something… I have tried the first initial suggestion, but have no idea how to “add” the additional code and module suggested by Ged Warren.
Thank you all for your contribution!
Cheers
Christine
Hi Christine
I know this sort of thing can be difficult if you are new to it. Did you try downloading the file above? Patrick managed to download it. Working examples are helpful if there is a practical spin on it and the file should mean that all you have to change in the range which is relevant to your problem. The file has an example of a sheet which changes the merged cells so it auto-fits whenever the typing is finished in that cell.
It works for most of the people above because the coding is sound. If you could download the file and play around with this line in the code if you have single cells;
ar = Array(“C5”, “C7”, “C9”, “C11”, “C13”, “C15”)
If you have a range of cells then the file deals with that too.
Post back if you need further assistance.
Take care
Smallman
Hi Patrick
You could run a loop outside of your loop for all 4 ranges. You do know that you will be bound within the row but the cell which has the largest amount of text in it. That is to say that the other cells in for exaple Row 20 will all be resized to the largest amount of text in the row. Have a play with the following. I am going to attempt to use Code tags in this thread for the first time and you can not go back and edit posts once made so if the [code] appears at the start of the code just ignore it. I am trying to get some indenting in my code which is sadly missing.
Sub FixMergedRng() 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 Dim j As Integer Application.ScreenUpdating = False 'Cell Ranges below, change to suit. ar = Array("G71", "K71", "V71", "Z71") For j = 0 To UBound(ar) For i = 20 To Range(ar(j)).Row On Error Resume Next Set rng = Range(Range("C" & 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 Next j Application.ScreenUpdating = True End SubFor your second issue you can’t make the Undo come back after you have run a macro. It clears the memory. So you need to be careful how you use the coding.
Hope this helps.
Take care
Smallman
Hi Noel
I just clicked on the link without even logging in and I managed to download the file. It should work without issue. If anyone wants to comment on the ability to upload the file that would really help others.
Take care
Smallman