Need a row to autofit height after it's value changes

[Guest]

I am trying to configure a worksheet to automatically adjust it's row heights
after the values have changed. Some of the formulas on the page are simply
pulling text off other cells in other tabs. When the text in those other tabs
is changed to a larger or smaller size, the row height on the original tab
changes, but not the row height on the sheet that has the formula, even
though both tabs have Automatic Row Height selectd.

 

[Dave Peterson]

You could have an event macro resize the rows/columns whenever the worksheet
recalculates. But be aware that these kinds of things will kill your edit|Undo
ability.

If you want to try, right click on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
End Sub

 

[Gord Dibben]

You must also have Wrap Text enabled on those cells with formulas.


Gord Dibben MS Excel MVP

 

[Guest]

That's exactly what I was looking for. I'm amazed that autofit doesn't
autofit in this case. Thanks for the help!

 

[Guest]

Good point - I do, but that would have been an issue.

 

[Guest]

Hmm. this works great, but if I put it on more than one tab, I get runtime
errors - 1004 - autofit method of range class failed.

Is it the Me keyword? If I replace it with the sheetname, is that all I need
to do?

 

[Dave Peterson]

The me. keyword refers to the object owning the code. In this case, it's the
worksheet.

Any chance that your worksheet that's causing the error is protected?

Are you doing this for each worksheet in the workbook?

If yes, you can remove the worksheet_calculate event from all the worksheet
modules and just use a workbook event.

This goes behind the ThisWorkbook module (not behind a worksheet):

Option Explicit
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.EnableEvents = False
Sh.Rows.AutoFit
'or be specific
Sh.Rows("1:33").AutoFit
Application.EnableEvents = True
End Sub

You may want to try this, too:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....