Re: Wrap text does not adjust row height in merged cells.

[Gord Dibben]

Long audible sigh here.................

One more victim of "merged cells".

Wrap Text works fine on merged cells, but Autofit does not work.

You need VBA event 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 Mon, 5 Feb 2007 12:02:40 -0500, "Paul S. Natanson"
<paul6...@concentricxxx.netxxx (Remove xxx's for real address.)> wrote:

>When I set the format/alignment of a SINGLE (unmerged) Excel cell to select
>"WrapedText", the row height adjusts upward, automatically, to accommodate
>the greater height needed by the wrapped text. (That's good/)
>
>But if I select "WrappedText" for a MERGED cell (e.g., a cell made by
>merging two side-by-side cells into one), the row height does NOT adjust.
>In that case, if the text runs onto a second line, I cannot see that second
>line unless I increase the row height MANUALLY. (That's bad.)
>
>So, my question is: "How can I force the row height to adjust AUTOMATICALLY
>when selecting "WrapText" in a merged cell that was made by merging two
>side-by-side cells?".
>
>PS: I am a proficient VBA programmer and macro writer and I am willing to
>write a macro to do this if I do not get any better suggestions.
>
>Paul S. Natanson
>1-908-630-+0406 (9AM-9PM eastern USA time)
>paul628 (at) concentric (dot) net
>February 5, 2007
>
>
>
>
>
>
>
>

[Paul S. Natanson]

Excellent! Thank you VERY much. It works great. I should have done this
YEARS ago. Many thanks. Paul

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:oftes2h2kmdnea5dr...@4ax.com...