linked cell contents not autofitting row height

[joeeng]

Why doesn't Excel 2002 autofit row height for text input from a linked cell?
When the linked cell contents exceed the currently viewable contents of the
recipient cell the row height is not adjusted. Is there a way to make the
autofit work in these circumstances?

 

[Dave Peterson]

Formulas don't change format and rowheight is a format.

You could use a worksheet event that resets the rowheights whenever that
worksheet recalculates.

If you want to try this, rightclick on the worksheet tab, select view code and
paste this into the code window.

Option Explicit
Private Sub Worksheet_Calculate()
Me.Rows.AutoFit
End Sub


Then back to excel and test it out.

 

[joeeng]

Unfortunately, that recalculates the autofit for rows that should not be
recalculated and also reveals the hidden rows/columns.

 

[Dave Peterson]

Is the range of rows that can be autofit known?

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("a1:a5,a12:a15,a99:a1000").EntireRow.AutoFit
Application.EnableEvents = True
End Sub

Some versions of excel will recalculate when you adjust the rowheight (that's
why I disabled events in this version--should have done it in the first, too.)

 

[joeeng]

Thanks, this works, but it does seem to be interfere with the workbook
routine BeforePrint when doing print preview. Things do a lot of flashing on
the status bar at the bottom of the screen.

 

[Dave Peterson]

I'm not sure what your _beforeprint routine does, but maybe you could disable
events there, too.

Thanks, this works, but it does seem to be interfere with the workbook
routine BeforePrint when doing print preview. Things do a lot of flashing on
the status bar at the bottom of the screen.