Set Row Height According to Number of Text Lines in a Cell

[emedley]

Background


I regularly use Excel to contain blocks of text in a particular column. The properties of the cells in that column have word wrap selected.

As text is entered and/or line breaks inserted (alt/enter), the row height normally increases automatically so that all text in the cell remains visible (up to max height value of 409.5 (546 pixels))

Some cells would contain just 1 line of text, others will be have 200 lines.

So far, so good...


Problem

Ignoring the max row height limitation, there are conditions where Excel doesn't always automatically increase a row's height sufficiently. This is where there are other cells to the left or right and/or where merge is used somewhere.

The problem we see is 'letterbox' syndrome, which is where text, usually at the bottom of a cell, is hidden from view. Even worse, it's not always obvious that any text is hidden.

If I manually double click on the bottom of a row (when the cursor changes to a cross) the row height should automatically set itself to reveal all lines of the cell's text. But, this does not always work; it seems to be reliable only when the spreadsheet is clean of other data.

The only solution I have is to manually drag the row down as far as necessary to reveal all of a cell's text. This is tiresome and prone to error when I have several hundred rows. It's even worse when I have to change the text as the row height has to be manually adjusted again.


Solution Approach

I don't think formulas will work here and the solution I'm hoping for is around this thinking, using a macro:

I manually enter into a file somewhere the row height that I would like Excel to set for a given number of lines of text in a cell.

Table example:
No. of Lines in a Cell | Row Height
1.............................. 20
2.............................. 25
3.............................. 40
etc..........................


The idea is I put the cursor at the start of a particular column and then invoke the macro. The macro runs down each cell, detecting the number of lines in a cell and sets the row height according to the above table.

One small complication: The row height depends on the font style and size. In other words, if my font was size 20, the row height would be much larger. However, this does not matter too much as I would be perfectly happy to manually adjust the set of row height numbers into the above table.

Also, if I reduce the column width this will hide more lines of text; conversely if I increase the column width, this would leave unnecessary space at the bottom of some cells. This does not matter though, as the solution is simply to run the same macro again.

Please could anyone help?

 

[Jaafar Tribak]

This hack worked for me :

Option Explicit

Function AdjustRowHeight(ByVal r As Range) As Double
    Application.ScreenUpdating = False
    With Worksheets.Add
        With .Range("a1")
            .WrapText = True
            .ColumnWidth = r.ColumnWidth
            r.Copy .Range("a1")
            .EntireRow.AutoFit
            AdjustRowHeight = .Range("a1").EntireRow.Height
        End With
        Application.DisplayAlerts = False
        .Delete
    End With
End Function


Sub Test()
    Sheet1.Range("A1").RowHeight = AdjustRowHeight(Sheet1.Range("A1"))
End Sub