[Erm]

Please help...this is driving me insane! I have an Excel spreadsheet and in one cell I have a large amount of text. This cell is a number of cells merged together and has Word Wrap on. It is also aligned horizontally as general and vertically as top.

The issue is that sometimes (only once) the Word Wrap seems to be working and the remainder of the time the last bit of text disappears! It is all there and appears correctly when editing the field. Any ideas?

Thanks

[bomb #21]

Merged cells are a PITA, basically.

When you say "the last bit of text disappears", would that be the "bottom line"?

Can you upload your file (or at least a copy of the offending bit) for inspection?

[Yorkshire Guy]

Hi Erm,

I see the same thing all the time!

Manually changing the row height (just drag the row number bottom line down) works to 'expose' the text, but often double clicking the same has no affect.

I hope someone else has a more definitive fix!

lol
Hew

[bomb #21]

That's it, in a nutshell -- merged cells are resistant to AutoFit.

A macro fix does exist, if you'd like me to dig it out.

[Erm]

The dragging does normally work...but not on this one....its very weird!...for the time being I have replace the offending information with a text box since is is like a disclaimer at the bottom of the spreadsheet. However, I do have other spreadsheets with the same issue, so if you could find me the macro fix, it would be greatly appreciated.

Debs

[bomb #21]

Dragging will work, but Format > Row > AutoFit will not (in my XP).

The macro is by Jim Rech (an MVP, I believe). If you need help installing it, post back.

----------------------------------------------------------------------------------------------------

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

[Zack Barresse]

I agree with Andy, don't merge cells. If you're wanting to add large text, either enlarge the columns/rows or use a textbox that moves with the columns/rows over which you place it. If you're making a form, Word may be a better application.

Edit: And yes, Jim R. is an Excel MVP.

[Erm]

Thanks for all your help guys....much appreciated!

[Zack Barresse]

Debs, if this solves your problem, don't forget to mark your thread as Solved, you can do so by going to Thread Tools | Mark Solved | Perform Action.

Take care!

[Erm]

I was looking where to do that when I made my last post but couldn't find the option...Thanks!

[Billman64]

Only the last line not showing? Sounds like Word Wrap has a limit to the amount of text in an Excel cell. I'm experiencing the same thing, but have a different solution.

While in editing mode for that cell, put the text cursor somewhere on the last line of the text and hit Alt + Enter to insert a line break.

The thing is now that part of the cell won't auto-adjust if you change the width.