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

2 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