changing the row height to fit

[Guest]

Can anyone please tell me if there is a way to automatically arrange the
height of a row to be adjusted to the text.
I have a summary sheet that is manually altered based on the validation cell
that is chosen for each situation. This changes the text that goes into the
cell.
The cell where the validation goes are merged cells and the text is wrapped.
Is there a way to automatically set the row height, for the entire sheet if
possible, by writing a macro or some other routine?
Thanks

 

[Guest]

You don't need any crazy formula. Assuming that all of the text in the
worksheet is the same size, just select the whole thing and change the font
size. That should bump everything up to the right size. I do that with huge
worksheets, then I go through and make the headings bigger/bold them in each
section (if it's requested).

 

[Gord Dibben]

Stephen

Long audible sigh here.................

One more victim of "merged cells".

Wrap Text works fine on merged cells, but Autofit does not work.

You need VBA event code or a macro to do that.

Here is event code from Greg Wilson.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP