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.
__________________
Smallman,
I am new to coding. I have a sheet that has 5 different sequential merged cell groups (D10:D13, G10:G13, M7:M10, O7:O10, Q7:Q10) then I also have 5 non-sequential merged cells (B46, B48, B50, B52, B54). If I run your code from March 23 I can get the non-sequential cells and one group of the sequential cells to autofit but not the others. I then tried using your code from December 4 but have had no luck getting it to work. I am guessing I am doing something wrong but I am not sure what. Any thoughts or recommendations to get all these cells to autofit?
Thanks,
Dave
Hi Dave
Did you download the file to see if that would help? Is your data merged from D10 to D13 if so maybe this;
ar = Array(“D10”, “G10”, “M7”, “O7”, “Q7”, “B46”, “B48”, “B50”, “B52”, “B54”)
With the ar line being the one to change. If your range is merged D10 to E10, D11 to E11 etc. Then perhaps something like this will work for you.
ar = Array(“D10”, “D11”, “D12”, “D13”, “G10”, “G11”, “G12”, “G13″,”M7”, “M8”, “M9”, “M10″,”O7”, “O8”, “O9”, “O10″,”Q7”, “Q8”, “Q9”, “Q10″,”B46”, “B48”, “B50”, “B52”, “B54”)
It is a bit long winded but should work. I have tested both methods on my computer and both go well. Alternatively you could run two procedures. One after the other. One for the sequential cells and one for the non sequential cells. That should work too. If you have any troubles just post back on the forum and I will supply an additional file.
Take care
Smallman
Smallman,
Thanks for the help and the quick response! I have downloaded the file and the “OnChange” helped me setup procedures to get a single group to work. Unfortunately, when I have tried to get 2 or more groups to work I have had no luck getting this issue resolved.
To clarify couple things from my previous post, my merged cells are D10 to F10, D11 to I11, etc. I am also wanting this procedure to run automatically.
Below are the two procedures I have tried that have given me the closest results.
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(“B46”, “B48”, “B50”, “B52”, “B54”, “D10”, “D11”, “D12”, “D13”, “G10”, “G11”, “G12”, “G13”, “M7”, “M8”, “M9”, “M10”, “O7”, “O8”, “O9”, “O10”, “Q7”, “Q8”, “Q9”, “Q10”)
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
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“B46, B48, B50, B52, B54, D10, D11, D12, D13, G10, G11, G12, G13, M7, M8, M9, M10, O7, O8, O9, O10, Q7, Q8, Q9, Q10”)) Is Nothing Then
FixMerged
End If
End Sub
With this procedure all of the “B”‘s and “Q”‘s will autofit as well as G11,G12 and G13. However none of the others will.
The other procedure(s) I have had some luck with is to separate out each group into its own procedures (b’s, d’s, m’s, etc. If I run these manually they work just fine but when I add code to automate it then I have the same issue as above. Here is the code for automating the procedures that I am trying to use.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“D10, D11, D12, D13, G10, G11, G12, G13, M7, M8, M9, M10, O7, O8, O9, O10, Q7, Q8, Q9, Q10, B46, B48, B50, B52, B54”)) Is Nothing Then
Call FixMergedD
Call FixMergedG
Call FixMergedM
Call FixMergedO
Call FixMergedQ
End If
End Sub
Again thanks for the help,
Dave
Hi Dave
Do you have a file? A dummy file if the data is sensitive. My email address is;
Marcusinlondon1ATTLESyahoo.com
Obvo Attles is the @ symbol.
Take it easy
Smallman
Hi,
I discovered just few steps can solve the problem.
Step 1: set the HorizontalAlignment = xlCenterAcrossSelection, WrapText = True, MergeCells = False
After Step1, Excel autoFit row height.
Step 2: set MergeCells = True
Step 3: set the HorizontalAlignment = xlLeft
Sub Macro3()
Range(“A1:C1”).Select
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.WrapText = True
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlLeft
.WrapText = True
.MergeCells = True
End With
End Sub
Hope it is helpful
Excel Programmer
Great post, and once I saw what the code was doing it got me to thinking…. I was able to reproduce the same effect with a little less code.
In my case, I could limit the print area to the first 8 columns, otherwise this solution may be useful to you. I have several rows where columns D & E are merged. For example range D25:E25 are merged and D26:E26 are merged. My solution was to add a formula out in column BA: BA26=D26 and I set the width / font of column BA = width of columns D+E. Also need to make sure column BA is set to wrap. Entering text into cell D26 will not auto fit the row height, but double clicking on the row separator does auto fit it.
I then added code in the same Worksheet_Change method:
If Target.column = Range("sqDescription").column then
Target.EntireRow.AutoFit
End If
For this to work, I had named the table heading “sqDescription” so I knew if I was in the right column or not. You may need to add additional checks to make sure you are in the correct rows. In my case I didn’t need to do this. If you add the code, it has the same effect of clearing out the Undo list. But if you don’t mind the manual auto fit clicking of rows, you can skip the code entirely.
I finally got the height to expand on my merged cells. But if the user changes the next for the next time and it is smaller then the row should go back down to a single row. I can’t seem to get that part to work. Any thoughts on what I might be doing wrong?
Thanks