Can I auto format row height according to requirements of largest cell in row?

[Chris Mitchell]

I have a worksheet where rows can be either 1, 2 or 3 lines high. I find
that on occasion I lose visibility of the end of the contents in some cells
because of reductions in row height, or because the contents wrap within the
cell.

Can I auto format row height according to requirements of the largest cell
in each row?

If Y how?

TIA.

Chris.

 

[Niek Otten]

Hi Chris,

Format>Rows>Autofit

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a worksheet where rows can be either 1, 2 or 3 lines high. I find
| that on occasion I lose visibility of the end of the contents in some cells
| because of reductions in row height, or because the contents wrap within the
| cell.
|
| Can I auto format row height according to requirements of the largest cell
| in each row?
|
| If Y how?
|
| TIA.
|
| Chris.
|
|

 

[Chris Mitchell]

Thanks Niek.

Far too easy!

 

[Chris Mitchell]

Spoke too soon.

Tried various combos of selecting multiple sheets, multiple rows on a single
sheet and a single row, but Format > Row > Autofit doesn't work. I still
lose visibility of the end of long, wrapped cells.

Anyone got any other ideas?

 

[Gord Dibben]

How long is "long"?

Excel allows 32,767 characters in a cell but will display only 1024 of them with
default settings.

You can increase this adding an Alt + ENTER every 100 chars or so.

Also making the font smaller helps a bit.


Gord Dibben MS Excel MVP

 

[Chris Mitchell]

Long is not that long, less than 1000 characters. I can cope with the
length by formatting cells to wrap, but I need a way of automatically
increasing the height of the cell everytime the text within in it wraps.

I envisage some form of formatting that says if cell contains 1 - 200
characters make height 12.75 pixels, if cell contains 201 -400 characters
make height 25.5 pixels, if cell contains 401 - 600 characters make height
38.25 pixels etc, but don't know if this is possible, and if so how to do
it.

 

[Gord Dibben]

Chris

That's how row>autofit and wrap text work.

Add more text, row adjusts height to fit as long as wrap text is enabled.

If you set all the cells in the row to autofit and wrap text, the cell with the
most text will govern the row height.

BTW: row heights are generally measured in points...............72 points to the
inch.

12.75 points won't show much text unless you have a very wide column.

You could use event code to adjust the row heights to a specific size based upon
string length of a cell.

Something like this.......................

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("B:B"))
n = Target.Row
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput

Select Case Len(rng)
Case Is <= 200: Num = 12.75
Case Is <= 400: Num = 25.5
Case Is <= 600: Num = 39.25
Case Is > 600: Num = 53.25
End Select

Excel.Range("B" & n).RowHeight = Num

Next rng
endit:
Application.EnableEvents = True
End Sub


Gord

 

[Chris Mitchell]

Doh!

Got it. I have to select the entire Row then apply autofit.

Don't work if you try to apply it to individual cells within the row.

Thanks Gord & Niek.