Gord:

I discovered this evening that if I record a macro with a shortcut key to
reinsert the VLOOKUP function already in the cell that this works. Thanks
again for all your help!

"Gord Dibben" wrote:

Worksheet_change event is not triggered by a calculation.

Maybe worksheet_calculate but I don't have time to test.


Gord

On Wed, 1 Aug 2007 19:30:02 -0700, MGC wrote:

Here's another stumbling block I believe I failed to mention. The merged
cells have a VLOOKUP function in them. If I enter a number to be 'looked
up', then go back and insert the same function again, the row will
expand...otherwise it will stay as one line. Hope you can help.

"Gord Dibben" wrote:

Thanks for the feedback.

Greg will be pleased.


Gord

On Wed, 1 Aug 2007 17:30:01 -0700, MGC wrote:

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

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

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?