Friday, June 25, 2010

Microsoft Excel does not wrap text in a merged cell. Solution.

 

The Background:

If you type more text than will fit in a cell, the contents will automatically spill over into the next cell:
clip_image002

However, if the next cell over has anything in it, then it won’t spill over:
clip_image004

Wrap Text to the rescue! Enable Wrap Text and your problem is solved, right?
clip_image006

 

The Problem:

What if you have merged cells that you want to wrap text with? You’d think it wouldn’t be a problem but even Excel 2010 can’t handle it:
clip_image008
It’s too big! And if you did manually shrink it, it won’t expand as you type in more text:
clip_image010
It is incredibly frustrating to have Microsoft Excel not wrap text properly just because there is a merged cell.

 

The Solution:

I found a few different plausible solutions including making a “sister/mock” cell in the same row that is not merged and whose width is slightly less than the merged cell. It didn’t work well for me and seemed like a sloppy workaround.

The best solution I found was VB code (don’t panic) that just makes everything work as it should out of the box.

Right-Click on your sheet tab and select View Code
clip_image012

Paste this code into the window and then close the window:

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

Credit for this code goes to Greg Wilson (as it was given on the forum I found it)

clip_image014

That’s it!

Now as you make changes to a merged cell, it’s height will automatically increase or decrease as it should have in the first place.
clip_image016

3 comments:

  1. Great stuff. thanks it works!

    ReplyDelete
  2. Finally an actual fix, the "change cell formatting to General" fix never worked for me.

    ReplyDelete
  3. This is awesome! Thanks so much for sharing. This has caused me angst for so long...

    ReplyDelete