Dear Gord,

Further to your advice, we used the following code in a macro to
automatically resize rows:

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

Although we thought it worked a treat at first, further use reveals that if
we change a cell in column B to something shorter, it resizes to fit that
cell (eg. to a row height for one row of text), but if column A has two rows
worth of text, it hides one of those rows. You then have to manually click
in the cell in column A and then out again to get it to automatically fix it.
As this is an organisational template, we can envisage a lot of people
finding this frustrating. Do you have any suggestions on how we could
overcome this issue by adjusting our code?

Any assistance would be gratefully received.

Regards
JD2


"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