Autofit rows where a cell contains results of formula

[Geoff C]

I have a column of cells in a new worksheet. Each cell has a formula that
concatenates data from several cells in the corresponding row of another
worksheet, and results in a comment ranging from 12 characters to around
about 160.

I need my new sheet to autofit the rows, since the longer text strings
overflow the fixed column space. However, they don't do this
automatically,even though I have formatted the cells as "Wrap", and have
already formatted the rows as autofit. I have to manually select all rows and
format them as autofit again, presumably because the strings are the results
of formulae, rather than entered text. Because it occasionally cuts off at a
point where you can't tell that there is overflow text, I need this autofit
to be automatic. Can this be done?

Any suggestions gratefully received,
Geoff

 

[Dave Peterson]

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

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, ....