Cell auto height, limited to cell height of 255.

[Ten98]

Hi all, my macro produces reports containing a lot of text in certain cells, and some of the cells are merged, preventing me from using the built in Excel "Auto height" function.

I found a sub that on this forum which automatically sets the row height so that all of the text in the selected cell fits in the visible area, even if the row contains merged cells:

Sub AutoFitMergedCellRowHeight()
    Dim CurrentRowHeight As Long, MergedCellRgWidth As Long
    Dim CurrCell As Range
    Dim ActiveCellWidth As Long, PossNewRowHeight As Long
    If ActiveCell.MergeCells Then
     With ActiveCell.MergeArea
      If .Rows.Count = 1 And .WrapText = True Then
        Application.ScreenUpdating = False
        CurrentRowHeight = .RowHeight
        ActiveCellWidth = ActiveCell.ColumnWidth
        For Each CurrCell In Selection
          MergedCellRgWidth = CurrCell.ColumnWidth + _
            MergedCellRgWidth
        Next
       .MergeCells = False
       .Cells(1).ColumnWidth = MergedCellRgWidth
       .EntireRow.AutoFit
        PossNewRowHeight = .RowHeight
       .Cells(1).ColumnWidth = ActiveCellWidth
       .MergeCells = True
       .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
         CurrentRowHeight, PossNewRowHeight)
      End If
     End With
    End If
End Sub

It's pretty great, but it seems to be limited to a height of "255", so cells with a LOT of text still aren't completely displayed. Can anyone tell me why this might be? Is there a way to increase the limit?

 

[shg]

There's a limit to the amount of text you can display in a cell (about 1024 characters, more (many more) if you have interpersed line feeds). Row height won't autofit beyond that.

The maximum row height is 409 points (about 6.7")

 

[Ten98]

So is Autofit itself limited to 255 points? because the rows with a lot of text are all cut off at exactly 255...

 

[shg]

No, rows autofit for me to the full extent, subject to the text length limitation -- for unmerged cells, anyway (I don't merge cells).

EDIT: As an alternative, you could put a formula in an adjacent column (the same width as the merged columns) that copies the text from the merged cell, and then autofit on that.

 

[Ten98]

I don't use merged columns either wherever I can avoid it, beleive me if it were an option here I'd un-merge them.

Since you don't use them, I guess you don't realise what the exact problem is here... If you use Autofit on a row which has merged cells anywhere in the row, it doesn't work. Hence the need for this rather complex routine to autofit rows which contain merged cells.

Moving the text to an adjacent cell and using vanilla Autofit doesn't help, and in fact the text I'm concerned with is already in an un-merged cell, it's the merged cell a few columns to the right of it that is causing the problem.

Since Autofit is not limited to 255 points, it must be the sub I'm using which is limiting it, which leads me back to my original question, can anyone see anything in the code I posted which would limit the Autofit to 255 points?