Excel – Autofit height of a merged cell in Excel 2010

microsoft-excel-2010

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.

Best Answer

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
Related Question