AutoFit Merged Cells Row Height Update 20151203

Way back in June 2012, I posted some sample code for adjusting the row height in merged cells. It's been 3-1/2 years, and people are still commenting on that article!

Apparently it is a common problem, and even though I don't like merged cells, sometimes we just have to deal with them.

AutoFit Merged Cells in Excel

The AutoFit Problem

To quickly summarize the problem – if cells are merged, the rows don't AutoFit correctly when you double-click in the row button area.

For example, the text doesn't fit in the merged cell below.


When I double-click the line between row buttons 10 and 11, the row height is reduced to fit one line of text, instead of expanding to fit all 3 lines of text.


To show the full note in the merged cell, I have to manually adjust the row height.

That's why I created a macro to automatically adjust the row height for merged cells.

Help With Questions

Throughout the comments in the original blog post, Smallman has answered many questions, and adjusted the code to meet new requirements, such as multiple merged ranges on a worksheet. Thanks Smallman!

Recently, he posted a new version of the code, and included a link where you can download his sample file. To make the code easier to find, I've put it in this update article, so it isn't buried in the comments!

Notes on Using the Code

Warning: Like other macros that change the worksheet, this code will wipe out the Undo stack, so you won't be able to undo any steps you've previously taken. If other people will be using the code, let them know about this!

In the original example, the code ran when the Order Form Note was changed – that triggered the Worksheet_Change event. You could use the workbook's BeforePrint event, to reduce the Undo problem. Or, use a button on the worksheet, like the one in Smallman's sample file.

Also, if your worksheet is protected, you can add code to unprotect and protect the worksheet.

Improved AutoFit Merged Cells Code

Below is Smallman's code, and his description of what the code does. I wrapped some of the lines, to make it fit better in the blog post. If you download his sample file, the code will look a bit different.

Go to the AutoFit Merged Cells with VBA page on Smallman's site, to download his sample file.

From Smallman's comment on the original article:

I have been working on a problem which has been raised quite a bit in this blog regarding the problem of when you have multiple merged cells in the same line. Nothing to date has dealt with this problem and I think I have an answer. The following will look at all cells in a given line and work out which cell has the 'most' text. It will then make that cell the big daddy and it will dictate how tall the row height is for the entire row.

For those interested in an example I put a new tab in the workbook on my own site as I can't upload files here. It works nicely. The tab which performs the magic is the red one at the end. Here is the coding for those interested.


Option Explicit

Sub MergedAreaRowAutofit()
Dim j As Long
Dim n As Long
Dim i As Long
Dim MW As Double 'merge width
Dim RH As Double 'row height
Dim MaxRH As Double
Dim rngMArea As Range
Dim rng As Range
Const SpareCol  As Long = 26
Set rng = Range("C10:O" & _
  Range("C" & Rows.Count).End(xlUp).Row)

With rng
  For j = 1 To .Rows.Count
     'if the row is not hidden
    If Not .Parent.Rows(.Cells(j, 1).Row) _
      .Hidden Then
       'if the cells have data
      If Application.WorksheetFunction _
        .CountA(.Rows(j)) Then
        MaxRH = 0
        For n = .Columns.Count To 1 Step -1
          If Len(.Cells(j, n).Value) Then
            If .Cells(j, n).MergeCells Then
              Set rngMArea = _
                .Cells(j, n).MergeArea
              With rngMArea
                MW = 0
                If .WrapText Then
                   'get the total width
                  For i = 1 To .Cells.Count
                    MW = MW + _
                  MW = MW + .Cells.Count * 0.66
                   'use the spare column
                   'and put the value,
                   'make autofit,
                   'get the row height
                  With .Parent.Cells(.Row, SpareCol)
                    .Value = rngMArea.Value
                    .ColumnWidth = MW
                    .WrapText = True
                    RH = .RowHeight
                    MaxRH = Application.Max(RH, MaxRH)
                    .Value = vbNullString
                    .WrapText = False
                    .ColumnWidth = 8.43
                  End With
                  .RowHeight = MaxRH
                End If
              End With
            ElseIf .Cells(j, n).WrapText Then
              RH = .Cells(j, n).RowHeight
              .Cells(j, n).EntireRow.AutoFit
              If .Cells(j, n).RowHeight < RH Then _
                .Cells(j, n).RowHeight = RH
            End If
          End If
      End If
    End If
End With
End Sub


AutoFit Merged Cells in Excel

You may also like...

13 Responses

  1. I do the following, without using macros.

    1 – Suppose that the total width of B10:E10 summed is 400 pixels.
    2 – Set column’s Z10 total width in 400 pixels.
    3 – Format Z10 cell’s to autowrap and font size and type to same as B10.
    4 – Write the formula =B10 in Z10.
    5 – Double click between row’s label 10 and 11 and the row height will autofit.

  2. Smallman says:

    Hi Felipe

    Thanks for sharing. While your method may work for you, the vast majority of people dealing with this problem have designed a form of some sort where others who may not possess much skill in the area of Excel will need to populate with text. Here is where your method might cause a problem.

    ‘Double click between row’s label 10 and 11 and the row height will autofit.’

    The above suggests the user perform an action. People tend to want the model to ‘autofit’ by itself with no user interaction. This is the driving feature. While I strongly agree with Deb regarding merged cells value (actually I think they are a legacy scourge inside Excel) they appear to be here to stay. Without Microsoft providing a solution to the problem, we have to do our best to create something useful.

    Thanks Deb for starting a fresh page on the topic.

    Take care


  3. Ian G says:

    Smallman – could you add an event to trigger after update that would autofit the target row? I think that would allow Felipe’s way to work, though the code above will be more flexible in handling multiple ranges within the worksheet.

  4. Smallman says:

    Hi Ian

    If Felipe’s technique was to be added to code it would look as follows.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [B10]) Is Nothing Then [B10].EntireRow.AutoFit
    End Sub

    This handles a single cell though and you have to follow the instructions from his post (points 1-5).

    This coding will handle the addition of a second row.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [B10:B12]) Is Nothing Then [B10:B12].EntireRow.AutoFit
    End Sub

    This may assist people if you have a single cell to test. However if you want to trap the grand daddy of a single row your code will need to be more complex.

    Take care


  5. Rick S says:


    The problem I am trying to solve is that I have text that I copy from a word doc and paste it into a merged group of cells (A11:F16);(via VBA) but at times the text I am pasting into the merged cells contains more lines of text than the group of merged cells (A11:F16). All of the copied text gets pasted in cell A11 and this group of merged cells autowraps the text…

    Currently I have to drag Row 16 to expose the hidden text (I currently have a MsgBox ask if I need to drag Row 16 lower to expose the text that is hidden, i.e.,
    vbExclamation, _

    Can the code included on this page be massaged to help with detecting if the merged cell box (A11:F16) needs to be extended to expose the hidden text?

    Any help will be greatly appreciated.

    Thank you.

  6. Alex F says:


    I am a Excel novice and am trying to incorporate this code into my worksheet, but so far I am not seeing the desired autofit. Smallman’s ‘Fit merged cells on change’ seems perfect but I cannot get it to work for me…

    I have merged cells C24-O24 and would like these to autofit. There will be rows below (likely C26-O26, C28-O28, C30-O30 and C32-O32) that I would like to autofit too. I am probably incorrectly altering your code or something stupid.

    Any pointers would be greatly appreciated.

  7. Melissa Haworth says:

    I have chosen to use the code provided by juan sanchez on smallman’s site. I’m struggling to figure out how to code in protection. The code runs flawlessly when the sheet is uprotected, but once I protect it, it fails to complete, and I need it to be protected. Any suggestions on this would be greatly appreciated. Here is the code Juan provided that I’m using:

    Option Explicit ‘New take on the above improves the autofit merged cells technique.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Vhight As Single
    If Target.WrapText = True Then
    With Target
    .RowHeight = 1
    .WrapText = True
    Vhight = .Width * .Height / Selection.Width
    If Vhight < 16 Then Vhight = 16
    .RowHeight = Vhight
    .VerticalAlignment = xlCenter
    End With
    End If
    End Sub

  8. thibaut says:

    your last code working only if adding new entry to get the cells and row wider and longer but in case on the same document you delete the last entry it will not reset the row size.
    Could you explain why?
    thanks a lot

  9. Matt says:

    This is great – thanks. I found that I needed to ensure the correct font size was copied to the temporary cell so that the correct height was set:

    With .Parent.Cells(.Row, SpareCol)
    .Value = rngMArea.Value
    .Font.Size = rngMArea.Cells(1, 1).Font.Size ‘Added this line
    .ColumnWidth = MW

  10. Henrik says:

    I managed to get this code to work in my worksheet. But what do I do to make the code work automatically?
    I have used this code in a form for my colleagues to use and they are a bit novice and i don’t want them to have to click the macro-button in order to get the code doing it’s thing.
    How do I do so that the code works it’s magic either directly after they’ve been filling a cell so it needs to be expanded or just before printing/saving the file?
    Thanks in advanced!

  11. Greg says:

    I found a simple solution that combines what I feel is a combination of many of the items here but individually does not solve the problem. Philip is probably the closest but easier done need to do this with code.
    1.Copy the contents of the merged cell to a single cell where the single cell width = sum of the merged cells width.
    2.Autofit that row
    3.Read the row height
    4.Delete Copied Data
    5. Set row height to the read row height

    Here is my subroutine hopefully you get the gist.

    Cells(i, 27).Value = Cells(i, 2).value
    Rows(i).RowHeight = 409
    NewHeight = Rows(i).RowHeight
    Cells(i, 27) = Null
    Rows(i).RowHeight = NH

  12. Taka says:

    Hi, I am super beginner of VBA so I’m afraid to ask you some fundamental things but it would be so helpful if you could let me know how I can adopt the code you showed above for the following situation.

    I have the data with merged cells B3:J3 to B1306:J1306 and wrapped text.
    I tried to auto-adjust to show whole text in each cell by running the code above. But didn’t work, nothing happened.
    I thought “Const SpareCol As Long = 26 Set rng = Range(“C10:O” & _Range(“C” & Rows.Count).End(xlUp).Row)” ‘s 26 or C10 should updated to 1306 or B3 but didn’t work.

Leave a Reply

Your email address will not be published. Required fields are marked *