1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Word Wrap inconsistencies

Discussion in 'Archive: Business Applications' started by Erm, Dec 4, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    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
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,269
    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?
     
  3. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    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
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,269
    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.
     
  5. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    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
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,269
    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
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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. :)
     
  8. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    Thanks for all your help guys....much appreciated!
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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!
     
  10. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    I was looking where to do that when I made my last post but couldn't find the option...Thanks! :)
     
  11. Billman64

    Billman64

    Joined:
    Aug 26, 2008
    Messages:
    1
    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.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/523873