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.
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.
Automatically Adjust Row Height
To fix the worksheet, so the merged cells adjust automatically, you can add event code to the worksheet.
The merged cells are named OrderNote, and that name will be referenced in the event code.
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 something that you'll use in your workbooks? Please let me know in the comments.
__________________
Sweet idea. Couple of suggestions:
1. Might pay to set out in the instructions above that this code is set up to work with a protected worksheet. So you need to make sure that any input cells (including the OrderNote range) are not locked - otherwise you will be locked out of them once the code runs. To do this, select all the input cells in the form, unlock the worksheet, push Ctrl + 1, and on the Protection tab of the Format Cells dialog box untick the "Locked" option.
2. This code wipes out the undo stack. This could really annoy users if they have made a mistake, and want to change something (I had some annoyed users due to this very issue for a form I built some time back). Given that the stated purpose is to ensure instructions are printed, how about triggering it only when printed, via a Before_Print event? That way, the undo stack only gets wiped after Print is pushed.
Thanks Jeff! I've updated the article.
I am curious as to why you declared the "ws" variable, assigned the ActiveSheet to it and then used it as the object of the With statement? Since it is only two letters long, couldn't you have avoided declaring the "ws" variable and, given that we are inside event code, simply used the two-letter long keyword "Me" for the object of the With statement instead? Given you did do it the way you did, why not remove the keyword "Me" from the calls to Protect and Unprotect and let their leading "dots" reference the With statement's "ws" argument instead?
As to the Protect/Unprotect issue, I guess it is possible for a worksheet to be protected except when run by an authorized individual who, through some set of macros and/or subroutines, unprotects the worksheet in an initializing macro and sets the protection back again in a log-off type macro... for that scenario, your code would leave the authorized user facing a protected sheet when there is still other macros and/or subroutines left to run all of which expect the protection to have still been lifted. To cater to this scenario (without burdening the normally expected setup), you could declare a Boolean variable, named say WasProtected to hold the worksheet's ProtectContents property and then test that at the end to see if the sheet should have its protection turned back on. I'm thinking of something along these lines (which would leave the worksheet's protection status at the conclusion of the code the way it found it at the beginning)...
With MeWasProtected = .ProtectContents
If WasProtected Then .Unprotect
'
' Your current code goes here
'
If WasProtected Then .Protect
End With
Thanks Rick, good points and I've updated the code.
Hej Debra,
works great, but I am not able to understand why I need to add "small adjustment to temporary width": MergeWidth = MergeWidth + AutoFitRng.Cells.Count * 0.66.
What problem does that line solve?
Thanks,
Johan
@Johan, that adjustment is the result of my testing, and without it, extra height is often added to the cell. By adding a bit to the width, the row height autofits correctly.
It's similar to those cells you might have encountered -- it looks like everything fits across, but when you try to autofit the row, Excel adds an extra blank line.
Hi Debra,
in my previous post on September 22, I was looking to understand why you have decided to do the mentioned adjustment to the width. This is still unclear to me.
What I have discovered now is however more important. Your code works fine in Normal view, and all lines become visible, but when I switch to Layout or Page Break view, some of the rows are again invisible, and the code does not change that fact in those views. Also when I print, the lines are not visible. So I thought the code solved the problem, but while the code works perfectly in Normal view and on the screen, in the other views and when printing not all lines are visible. Can this be solved?
Would be very greatful for your comment.
Thanks,
Johan
@Johan, try commenting out the line that adds the width adjustment, or reduce the multiplier from .66 to a lower number.
Johan,
Great code. This solved a huge headache of mine. I would just like to echo John's comment from June 7. I am using this in a form for a client so I would like to be able to protect the sheet, but when I do, two things happen: first, the merged cell gets oversized, then it gets locked. Any idea as to why this is happening?
Hi Debra,
This code is great, thanks for posting. Just one thing I discovered - it works fine if the cell just contains a value but, if it contains a formula displaying a value updated from another sheet, it doesn't do the autofit.
The solution is to add the following code to the Worksheet_Activate() event to force a recalulation:
Range("OrderNote").Select
ActiveCell.FormulaR1C1 = "='formula in OrderNote"
ActiveCell.Calculate
Say you have a sheet with merged cells (say two of them within range A2:A4), and they contain formulea that display values from other sheets, and those values may vary in length, you can have your merged cells auto-resize to fit the length of the values.
Put this code on the on activate event of the sheet your merged cells are in:
Private Sub Worksheet_Activate()
Dim r, c As Range, strF As String
Set r = Range("A2:A4")
For Each c In r
If c.MergeCells = True Then
If c.HasFormula = True Then
c.Select
ReSizeRow
End If
End If
Next
End Sub
And this code in a module:
Public Sub ReSizeRow()
With ActiveCell
.WrapText = True
.UnMerge
Rows(.Row).AutoFit
.RowHeight = .Width * .Height / Selection.Width
Selection.Merge
End With
End Sub
Hello. First, thank you for a very helpful post. My problem: I have more than one merged cell in my worksheet that I need to apply this too. How do I modify the VBA code above to include more than one "OrderNote"??? Thank you.
[...] Unfortunately Row AutoFit doesn't work with merged cells. For a VBA solution see: AutoFit Merged Cell Row Height | Contextures Blog [...]
I have the same issue as Rick which was posted on Nov. 30. I have multiple merged cells on my worksheet and need to how to modify the code to include more than one "ordernote". Thanks in advance for any insight on how to fix this.
Your code is great! Thank you so much. I also have the same issue as Rick (posted on Nov 30.) I have several merged note cells in one worksheet. How do you modify the code to include more than one merged note cell? Thank you very much for your help with this.
This is great! But I also having the same issue with having multiple merged cells in the same worksheet. How can the code be modified to include more than one Named Range? Thank you!
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 Sub
Hi 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
@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 Sub
@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.
Yes I did mention that or did you miss that bit? It is just above in black and white.
Take care
Smallman
Yes I did miss the bit where you said "Run this code only once", because also above in "black and white" is your statement With the example above you could use something like the following within your procedure to unlock the cells in question.
So if Suzi implemented your approach as you originally posted, then every nth time she runs her routine, your code addition does something that is not necessary.
Even had you not said that above, I don't understand why someone should run a macro one time to do something so trivial. To my mind, it's a bit like suggesting a VBA approach when someone asks "How do I make Cell A2 bold"
Hi Jeff and Smallman,
I apologize for the delay in replying. I was pulled onto another small project...
Anyway, I have read your posts and I understand how to unlock cells. This is a common routine used thoughout many forms I create.
I believe I may have missed out a part in my initial request. I noticed this when I looked at the code again. It seems the problem is happening because there are named ranges.
In the Array, rather than using cell names (i.e. C23, C24), I used named ranges. I have pasted my code below. The code works great but even though going into the code the cells are unlocked, by the time it finishes, the cells are locked up. I hope this helps. let me know if you need further clarifications. Thanks!
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
ActiveSheet.Unprotect "password" ', userinterfaceonly:=True
Application.ScreenUpdating = False
'Cell Ranges below, change to suit.
ar = Array("RAFDesc", "Summary", "AssExcRisks")
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
ActiveSheet.Protect "password"
End Sub
Hi Suzi
Welcome back. That was quite a break.
Before you protect your sheet you will need to unlock the cells in your named ranges. So in the lines before the protect statement include something like the following;
Range("C47:J47").Locked = False
Do this for each of your 3 named ranges. This will give you the restult you want.
Take care
Smallman
Hi Smallman! Everything is working fabulously! I was so close when I testing prior to my reply... I was trying to unlock the named ranges rather than the range of cells.DOH!! Thanks again! You have been invaluable!
Regards, Suzi
Hi Suzi
It becomes a little more complex when working with named ranges to unlock the cells. On testing I assumed your named ranges were 1 cell in length, from here you merged the cells. What you have to do is cycle through all of the merged cells and unlock them one at a time. Add the following to your code and it will unlock the cells of your named ranges. I would refer to this procedure just before you add the sheet protect button or just copy everything under the Sub line and before the End Sub line.
I have put the following in as a stand alone procedure. I hope you will be pointed in the right direction from here.
Take care
Smallman
Option Explicit
Option Base 1
Sub UnLockIt()
Dim arr As Variant
Dim r As Range
Dim i As Integer
arr = Array("RafDesc", "AssExcRisks", "Summary")
For i = 1 To UBound(arr)
For Each r In Range(arr(i))
r.MergeArea.Locked = False
Next r
Next i
End Sub
[...] AutoFit Merged Cell Row Height | Contextures Blog [...]
Brilliant! Thanks Debra! I've been trying to figure this out for a couple months now! Thanks again!
First, I want to sincerely thank Debra and others who have provided this very helpful code. It resolved an issue I've had with a spreadsheet for some time. I am more of an analyst and not programmer, but find this process somewhat interesting and would like to know a little more about how this works.
I find that when I use this merged cell resizing code in a fairly complex worksheet (actually multiple sheets within a workbook) which includes password protection, that everytime I make a change to any cells in the worksheet, the ActiveSheet.Protect statement "re-protects" my sheet.
Is there an easy way to change the original code so that it will only resize with a change to the "OrderNote" cell? This would allow me to make changes to the template without it always going back to protected status.
Thanks
Chuck