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]

Couldn't reproduce the problem you described when applying word wrap

You didn't mention the Column width ... Is it the standard column with for all cells ? And what about the worksheet zoom ?

 

[emedley]

Couldn't reproduce the problem you described when applying word wrap

You didn't mention the Column width ... Is it the standard column with for all cells ? And what about the worksheet zoom ?

Column Width
The column width would not normally be the 'standard' width. I would normally have adjusted it, as necessary, to be 'best fit' with the whole spreadsheet. However, I think we can ignore Column Width as the macro only needs to read the No. of lines of hidden text.

Zoom
We can ignore the zoom factor because this has almost no perceptible impact; everything is kept in the same proportion.

Detecting No. of Lines of Hidden Text - Additional Point

A cell's text can be vertically aligned top, middle or bottom. This means any 'hidden' lines of text (where the row height is set too small) would not necessarily always be at the bottom only.

 

[shg]

The macro runs down each cell, detecting the number of lines in a cell and sets the row height according to the above table.

There's the rub; if that can be done at all, it would require API (in which case Jaafar's your guy).

Excel spends all of its creativity typesetting the font face, size, and style of style Normal; other faces, sizes, and styles, not so much. It doesn't pretend to be a word processor, which it sounds is what you need.

 

[emedley]

There's the rub; if that can be done at all, it would require API (in which case Jaafar's your guy).

Excel spends all of its creativity typesetting the font face, size, and style of style Normal; other faces, sizes, and styles, not so much. It doesn't pretend to be a word processor, which it sounds is what you need.

Thanks for the advice and I understand your reasoning.

The column (and many rows) of text I mentioned are, in fact, short descriptions of a system's many Requirements. I use the ‘normal' font in Excel although sometime shrink it to size 8 or 9. The reason for occasional row heights ~200 mark is when there is an itemised list for a particular requirement.

A word processor wouldn't fit the bill though on this occasion. The Requirements are just text but, more importantly, I have several columns to the right of the column of text, which store various values for each Requirement. Other cells do calculations on these values, which is why Excel is the best-fit tool overall or ‘least worst’ maybe - and a d/b is not so quick to adapt as Excel.

Thanks again for taking the time to consider and review my problem

 

[Jaafar Tribak]

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.

Have you considered temporarly copying the cell and pasting it in a blank worksheet , appply the row autosizing there and retrieve the resulting Row height which you can then apply to the original cell ... Once done, delete the copy ... The whole process can be automated in a macro ... It is a tacky and slow workaround but ,I think, it should work

Alternatively, we can use an API based workaround to get the number of text lines in a cell (works regardless of font size and style) .. more complicated but cleaner and should be faster

 

[shg]

Have you considered temporarly copying the cell and pasting it in a blank worksheet

That doesn't address the issue that Excel doesn't typeset anything but Normal accurately, Jaafar.

 

[Jaafar Tribak]

That doesn't address the issue that Excel doesn't typeset anything but Normal accurately, Jaafar.

Hi shg,
I didn't understand :'Excel doesn't typeset anything but Normal accurately'

EDIT : I see what you mean

 

[shg]

A word processor wouldn't fit the bill though on this occasion.

Mail-merge.

 

[emedley]

Have you considered temporarly copying the cell and pasting it in a blank worksheet , appply the row autosizing there and retrieve the resulting Row height which you can then apply to the original cell ... Once done, delete the copy ... The whole process can be automated in a macro ... It is a tacky and slow workaround but ,I think, it should work

Alternatively, we can use an API based workaround to get the number of text lines in a cell (works regardless of font size and style) .. more complicated but cleaner and should be faster

Hi Jaafar, I hadn't considered those options but the cost of generating a solution and maintaining it may be too heavy for the overall benefits. So maybe I'll just live with it.

If this task I'm doing becomes more routine or more complicated to the point of being 'painful' then that may justify contacting you guys for a 'proper' solution.

I see that many users on other forums have the same requirement but there's no easy solution at the moment.