1

We have an Excel sheet that has various merged and non-merged cells on it, so it comes out arranged like a paper form. At the bottom are some cells that are merged all the way across the page because they can contain a lot of data. However, if someone writes more than can fit in the cell width, it wraps the text but does not display it (wrapping is enabled on these cells). Even if you double click the line between the cells (as you would to adjust the height automatically) it will stay at a single line high. I can manually adjust the height, but because we normally have the worksheet locked, the users can't do that.

Is there any way to make it adjust automatically on the merged cells? A setting would be best, but VBA is OK, too. I've tried code like Cells.EntireRow.AutoFit but that still only goes to a single line high.

5

Here's a Contextures Blog post, happily titled "Autofit Merged Cell Row Height"

I urge you to read the whole post, but here's the code:

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
1
  • 2
    Works like a charm. Sorry for the delayed accept. As seems to always happen, it was "urgent" when they came to me with the problem, then never seemed to care after that.
    – techturtle
    Jul 3 '12 at 20:06
0

I made add-in for Auto fit row height of multiple merged cells. Please use this, if you want to autofit row hight. [Release Ver2.6 · toowaki/AutoFitRowEx · GitHub] https://github.com/toowaki/AutoFitRowEx/releases/tag/2.6.2

1
  • Welcome to Super User! It's great that you wrote an add-in to address the issue, but please read how to recommend software in answers, particularly the bits in bold; then edit your answer to follow the guidelines there. Thanks!
    – bertieb
    Jun 9 '18 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.