The code only operates if cells are merged so no point using it.

I don't know if RowAutofit will react to a change in quantity of data when
the results of a formula.

I can't get it work.

This seems to do the job when calculation takes place.

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


Gord

On Wed, 29 Jul 2009 10:54:03 -0700, Debm
wrote:

I have a spreadsheet that is full of formulas pulling data from another
spreadsheet. The amount of text that ends up in the cells of the spreadsheet
with the formulas varies everytime. I tried using the code but it did not
work. I don't have any merged cells in either spreadsheet. Should the code
be different if there are no merged cells? Also all I did was View Code and
pasted in the code and then Alt Q to return to spreadsheet and then saved it.
Did I miss a step?

thx


"Gord Dibben" wrote:

Do you have any merged cells in the row?

Rows with Merged cells will not Autofit.

You need VBA code to do that.

Here is 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


Gord Dibben MS Excel MVP

On Thu, 24 Aug 2006 19:44:01 -0700, JD2 wrote:

Is there a way to override a manual row height setting, if you want word wrap
to work automatically in Excel?

We are setting up a template for users in our organisation to use, and want
this to happen automatically (ie. we don't want staff to have to adjust row
height themselves).

We have selected the rows (whose height had been previously altered
manually) and we chose Format Cells Alignment - Word Wrap on. We've also
chosen the Format, Row, AutoFit command. However, all the text cannot be
displayed unless we manually fix the row. Any suggestions?

Gord Dibben MS Excel MVP