Automatically Adjusting Row Height in Merged Cells

[Guest]

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?

 

[Gord Dibben]

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

 

[Guest]

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

 

[Gord Dibben]

Thanks for the feedback.

Greg will be pleased.


Gord

 

[Guest]

How can I make this work on 3 consecutive tabs in the same workbook?

 

[Guest]

I'm having a problem with the code not following through on all lines
(23-43)...hints?

 

[Guest]

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]

Worksheet_change event is not triggered by a calculation.

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


Gord

 

[Gord Dibben]

You could paste the code into each worksheet module.

Or you could place it in Thisworkbook module and change the event type to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Would then work on all worksheets.


Gord

 

[Gord Dibben]

No hints from me unless those rows are calculated values which do not trigger
change events.


Gord

 

[Guest]

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!

 

[Guest]

I've tried to insert the code as was suggested. When I go to test it out, I
get a Run-time error '1004': Unable to set the Column Width property of the
Range class, and it point to the row of code "e.ColumnWidth = MrgeWdth" which
is about half way through the code that was copied/pasted. The end result is
that the merge of the cells seems to be lost as the cells then have the grid
lines shown again for columns and rows and all of the text is placed in the
top left most cell and that cell in fact does have the row height auto
adjusted. Any ideas on why this happens?

 

[Gord Dibben]

Is the sheet protected?

When I protect the sheet and enter text in an unlocked merged cell I get the
same error message.


Gord Dibben MS Excel MVP