Wrap Text in Merged Cell - How?

[Robert M. Atkinson]

Excel 2007

If I merge several (or even 2) cells in a row, I can not get the text to
wrap when I hit enter. It works, of course, in a single cell.

How can I get the text to wrap in merged cells? I have checked "Wrap text"
in the Format Cells dialog box,

Thanks,

Bob Atkinson

 

[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

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

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP

 

[Robert M. Atkinson]

Thnaks Gord. I copied & pasted into the sheet module. When I try to enter
text into the merged cell, it gives me the following error:

Microsoft Visual Basic
Compile error
Sub or Function not defined

What might I be doing wrong?

Thanks again!

Bob

 

[Gord Dibben]

Could be you missed something when you copied/pasted.

1. Right-click on the worksheet tab.

2. Delete any lines of code that may appear in the module.

3. Copy the code from my post then paste into the blank module.

4. Format the cells to wrap text.

6. Set the rows to Autofit.

7. Start typing.


Gord

 

[Robert M. Atkinson]

Thanks,

I followed your instructions. I cleared everything out, then re-pasted the
VB code. The VB page then showed the copied code in the main window, with
the 2 drop down boxes at the top showing "Worksheet" & "Change". I then
clicked "File" & "Close and return to MS Excel".

I formatted the merged cells to wrap text. I then clicked on Format &
Autofit Row Height.

I no longer get the error from this morning, but the cells still do not
autofit the text. The text just cuts off at the existing cell boundries.

I am still missing something I guess.

Thanks,

Bob

 

[Gord Dibben]

First of all...........how much text are you entering in the cells?

Excel won't show more than 1024 characters, including spaces, in a cell.

If you exceed that, the text won't display.

OR

Quite possible that when you errored this morning events were disabled.

When in the module click on View>Immediate Window

Enter this in the window then hit ENTER

Application.EnableEvents = True

Back to Excel and see what happens when you type a long string in a merged cell.


Gord

 

[Robert M. Atkinson]

I am entering far less than the 1024 chractor limit... perhaps around 50 -
100 charactors.

I followed your instructions regarding the View>Immediate Window. This
didn't seem to help.

A bit more info: If I type in enough charactors to make the text wrap and
hit Enter, the text does not wrap... it just gets cut off. The pysical cell
size does not chage. If I then manually adjust the row height, the text
then wraps as it should. If I then select Autofit Row Height, it goes back
to the cut-off text in the original cell size.The Autofit Row Height doesn't
seem to recognize that the row height needs to increase.

Thanks,

Bob

 

[Gord Dibben]

Perhaps you could send me a scaled down version of your workbook?

Change the DOT and AT appropriately to email me.


Gord

 

[Robert M. Atkinson]

Sent... Thanks!

Perhaps you could send me a scaled down version of your workbook?

Change the DOT and AT appropriately to email me.


Gord