We help IT Professionals succeed at work.

Excel 2007 - Auto fit row height too narrow

kenbaker
kenbaker asked
on
I have a spread sheet (.xlsx) that contains and large amount of wrapped text.  If I size each row individually, to the correct height for the amount of text, everything is great.  However, if I use Auto Fit Row, the maximum height is about 135.  Is there a way with VBA or other method to auto fit rows that need to be larger than this default constraint?  

Comment
Watch Question

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
In the Related Solution cited at the bottom of your question, the accepted answer looped through each row in a specified range and performed an AutoFit for the row height. Excel should be capable of up to 409.5 points of row height. Is that macro not working for you?

Note that merged cells will cause problems in setting the row height. So too will protected worksheets and Shared workbooks.

Brad

Author

Commented:
No, the looping solution worked but my cells vary in height so much that setting a standard height is not practical.  I ran the VBA code in hopes that Excel would produce different results than those obtained through the ribbon.  In my sheet, one row may contain no more than a few characters and another row may contain thousands.  So to set every row to accommodate the height of the largest text would make viewing and printing difficult.  I was hoping to find a way to break through whatever barrier Excel seems to instate when running auto fit height.

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Could you post a sample workbook that illustrates the code's inability to function as you wish?

Author

Commented:
You will notice that row 2 and row 6 have more text than is being displayed.  Auto fit will not exceed this height.  In order to display and ultimately print this text, each row much be adjusted individually.  This is not a practical solution for the amount of data I have in this format.
So I am looking for a way to auto size to the amount of text int he field.


Auto-Fit-Row-issue.xlsx
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Excel 2010 will AutoFit the row height of your sample workbook even when I made cell A2 contain twice as many characters. Note that you still cannot exceed a row height of 409.5 points.

Excel 2003 won't AutoFit much past 1024 characters, that being the specified limit for how many characters are displayed & printed. Even if I use the trick of putting a line feed character at the end of each line past 1024 characters, I can't AutoFit much past 1024.

I'll need to check on my home computer (which has Excel 2007) to see what is possible there.

Brad
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
I checked out with Excel 2007 at home, and confirm that the AutoFit isn't going much past 1024 characters. This is apparently a "feature" of Excel 2007, and there isn't a workaround. http://www.pcreview.co.uk/forums/thread-3656106.php

Microsoft fixed the limitation in Excel 2010 (at least in the beta version I have at the office).

Brad
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
The code in the snippet is a Band-Aid that you can put over the problem. It increases row height (up to the 409.5 point maximum) if the cell contains more than 1024 characters on a pro-rata basis. Depending on the content of those cells, it may leave excessive white space or even hide some of the text--but it will definitely do better than nothing.

Brad
Sub RowHeighter()
Dim cel As Range, rw As Range, rg As Range
Dim sngHeight As Single, sngMax As Single
Dim nChars As Long
Set rg = Intersect(Selection, ActiveSheet.UsedRange)
nChars = 1050   'Maximum number of characters that will display. Adjust to suit, with 1024 as the minimum.
rg.Rows.AutoFit
Select Case Val(Application.Version)
Case Is < 12
    MsgBox "This code won't work much past 1024 characters per cell in Excel 2003 and earlier"
    Exit Sub
Case Is > 12
Case 12
    For Each rw In rg.Rows
        sngMax = rw.Cells(1).RowHeight
        sngHeight = sngMax
        For Each cel In rw.Cells
            If Not cel.MergeCells Then
                If Len(cel) > nChars Then
                    If sngHeight * Len(cel) / nChars > sngMax Then sngMax = sngHeight * Len(cel) / nChars
                End If
            End If
        Next
        If sngMax > 409.5 Then sngMax = 409.5   'Can't exceed a row height of 409.5 points
        If sngMax > sngHeight Then rw.RowHeight = sngMax
    Next
End Select
End Sub

Open in new window

Author

Commented:
Wonderful!  Thanks so much for the explanation! - One more reason to upgrade to 2010 :)
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Hilary,
Thanks for the kind words and grade!

Brad