Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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