Thanks Dave. I have a working solution - really appreciate the time you
spent putting this together.

M


"Dave Peterson" wrote in message
...
First, working with merged cells is a real pain.

They don't behave nicely in lots of situations (autofitting row height is
just
the tip of the iceberg!)

Second, excel doesn't keep track of what cells are changed because of a
calculation. So you'd have to look through all the cells with formulas
which
could be a pain, too.

Third, there's no easy way to find merged cells.

That said...

Jim Rech wrote a macro called AutoFitMergedCellRowHeight:
http://groups.google.com/groups?thre...%40tkmsftngp05

This could be modified to tie into a worksheet_calculate event.

If you want to try...

This goes behind the worksheet that has the merged cells with formulas in
it.

Option Explicit
Private Sub Worksheet_Calculate()

Dim myRng As Range
Dim myCell As Range

'define your range with the addresses of the
'merged cells formulas
'or give it a nice name
'Set myRng = Me.Range("a1,b3,c7")
'Set myRng = Me.Range("MyMergedCells")

'or look through all the formulas
Set myRng = Nothing
On Error Resume Next
Set myRng = Me.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'no formulas found
End If

On Error Resume Next
Application.EnableEvents = False
For Each myCell In myRng.Cells
If myCell.MergeArea.Cells.Count = 1 Then
'do nothing, not a merged cell
Else
Call AutoFitMergedCellRowHeight(ActCell:=myCell)
End If
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub

You'll want to use what's best for you in this portion:

'define your range with the addresses of the
'merged cells formulas
'or give it a nice name
'Set myRng = Me.Range("a1,b3,c7") 'Option 1
'Set myRng = Me.Range("MyMergedCells") 'Option 2

'or look through all the formulas 'Option 3 (next 4 lines)
Set myRng = Nothing
On Error Resume Next
Set myRng = Me.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

My preference would be to use the named range (select the cells and then
insert|name|define). Then I wouldn't be looking through all the formulas
with
each calculation. (If you move cells to a new location
(inserting/deleting rows
or columns), you'd have to adjust that list of addresses.)

You can comment the options you don't want--and uncomment the line(s) that
you
want to use.

This portion does all the work. It goes in a General module
(Insert|Module
inside the VBE).

Option Explicit
''based on Jim Rech's code
''http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight(ActCell As Range)
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActCell.MergeCells Then
With ActCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActCell.ColumnWidth
For Each CurrCell In .Cells
MergedCellRgWidth _
= CurrCell.ColumnWidth + MergedCellRgWidth
Next CurrCell
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

Notice the comment in Jim's code:

''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.





Michelle wrote:

I have some VLOOKUPs which sometimes return 50 or 60 characters (they're
in
merged cells). I want the text to wrap, and the cell to enlarge to fit
the
text - Just like it does whan you type it in, but somehow, because it is
being returned from a formula it just displays the bottom line of the
wrapped text, and doesn't resize the row.

Anyone know how I cam make it fit?

I'm happy to use a VBA solution if there's no built in way to make it
happen
automatically.

Thanks

M

--

Dave Peterson