Solved

Excel 97 - wrapping text in cells

Posted on 2000-03-20
26
748 Views
Last Modified: 2008-10-10
I have an Excel 97 (SR-1) spreadsheet and I cannot get the text to wrap in the cells. I have to manually resize the row height instead. Everything looks correct on the Alignment tab of the Format Cells dialog box.

I think the problem is that early on when I was building the spreadsheet I did a Ctrl-A to select all, and I resized all the rows/columns to suit what I was doing. Since overriding the default row height, wrap-text doesn't seem to work anywhere in the spreadsheet, even on otherwise unformatted cells way outside of the range I'm working in. But if I create a new sheet wrap-text works fine.

This is driving me batty! I have too much work invested to scrap the sheet and start over, but I don't want to resize hundreds of rows. Help! Please?
0
Comment
Question by:suzyQ
  • 10
  • 7
  • 5
  • +1
26 Comments
 
LVL 13

Accepted Solution

by:
cri earned 100 total points
ID: 2637127
If the text wraps but the height is not sufficient to display all lines: Have look into this MSKB article:
http://support.microsoft.com/support/kb/articles/Q149/6/63.asp
0
 
LVL 13

Expert Comment

by:cri
ID: 2637132
If the text does not wrap at all, try this fast and safe bet:
StartButton|Run excel /regserver  (there is a blank before the switch)
0
 
LVL 13

Expert Comment

by:cri
ID: 2637138
BTW: You can choose the wrapping _location_ with Alt+Enter, has the same function as Shift+Enter in Word
0
 

Author Comment

by:suzyQ
ID: 2637212
cri,

The Microsoft article confirms my guess about the cause of the problem.  Unfortunately, it says that "This behavior is by design..." and the only workaround is exactly what I DON'T want to do. (YUCK!!) I tried running Excel with the regserver switch but that didn't help. I don't know what you mean by your third comment, however. Can you tell me what "the wrapping _location_" is?

Thanks for your help, but I don't think there's a solution that will let me keep the work I've done so far, judging by the support article. I'll leave this question open for a while in case anybody knows something that MS ain't telling us poor users. If not, I'll accept your first comment, for educating me to not do what I did the next time I build a spreadsheet.
0
 
LVL 13

Expert Comment

by:cri
ID: 2637232
Do not understand: Why is the workaround i.e. using Format|ROW|Autofit not an option for you ?

As for the wrapping location: You might want to determine yourself where the wrapping shall occur. In the formula bar put cursor on the desired spot and press Alt+Enter. Try it out (on a test sheet...)
 
0
 

Author Comment

by:suzyQ
ID: 2637341
oops, sorry, I meant to say that the Autofit workaround suggested by MS doesn't work. So the only workaround I have is to manually resize the rows, which is exactly what I don't want to do. There is no pattern to which rows need to be resized, nor by how much, so it means doing them one by one. I don't know why Autofit doesn't work, it would make this problem much simpler for me if it did.

Thanks for the tip on wrapping location.  That's very handy to know. I can think back to many instances where that would have helped, so I'll surely be able to use that in the future!
0
 
LVL 13

Expert Comment

by:cri
ID: 2637403
Do you happen to have merged cells ? If yes:
http://support.microsoft.com/support/kb/articles/Q157/0/97.asp
0
 
LVL 13

Expert Comment

by:cri
ID: 2637415
If yes: Do not merge cells unless you can not avoid it, MSKB is full of articles as above.
 
If all you want to do is to align a title over some cells, do NOT use the [<-a->] icon, use Format|Cells|Alignment: Justify Over Selection
0
 

Author Comment

by:suzyQ
ID: 2637461
Yes, I have merged cells. I guess we know now why autofit won't work. (sigh...) I can't unmerge them because the ss contains several different tables of varying column widths. I think I'm up s*** creek...

Another good tip on aligning titles, though. Thanks, there'll be points for you even if somebody comes up with the miracle solution to my problem.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2637645
Well, I don't have any miracle solution to your problem, but perhaps I can make it easier for you to adjust the rowheights.

Set up the following macro, then use it on each cell you need to adjust.  Set up a hot key to make it easy  

Sub HtAdjust()
'
'   Copies the text from the currently selected cell to a 'scratch' cell formatted to wrap text.  
'   Get the row height, then format the original row with the needed height.

    Dim Ht As Single
    Dim CRow As Integer
    Dim CCol As Integer
   
    Selection.Copy
    CRow = Selection.Row
    CCol = Selection.Column
    ' Set up your scratch cells here.  You may want to use a second worksheet for this.
    ' this example uses cell GZ:200
    Cells(200, 208).Select
    ActiveSheet.Paste
    Rows("200:200").Select
    Ht = Selection.RowHeight
    Rows(CRow & ":" & CRow).Select
    If Selection.RowHeight < Ht Then Selection.RowHeight = Ht
    Cells(200,208).Select
    Selection.ClearContents
    Cells(CRow, CCol).Select
End Sub

Of course, you could go through and automate this to check all of your cells:

Sub AllCellAdjust()

  Const MaxRow = 50
  Const MaxCol = 30
  Dim R%, C%, S$

  For R=1 To 50
    For C=1 To 30
      Cells(R,C).Select
      S=Selection.Formula
      If Left(S,1)<>"=" And Len(S)>1 Then HtAdjust
    Next C
  Next R

End Sub
0
 

Author Comment

by:suzyQ
ID: 2637846
Okay bhess1, I've never done anything this in depth before in Excel, but I managed to figure out how to set up the macros and how to set up a hot key to run them (thanks to Excel Help). When I run them I get "runtime error 13, type mismatch". I clicked the debug button and it took me to the macro code where "S = Selection.Formula" was highlighted. I don't know if this is a typo in the macro, or if I didn't do it right. (I'm in a little over my head here.) What I did was record two separate macros using the names of yours, and then cut and paste your two overtop. Should it have been just one macro?
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2638094
Well, I noticed one problem in my code, and have a possible solution for your problem.  Try this:

Sub AllCellAdjust()

  Const MaxRow = 50 ' set to your maximum rows in the spreadsheet
  Const MaxCol = 30 ' set to your maximum columns in the spreadsheet

  Dim R%, C%, S$

  For R=1 To MaxRow     ' Changed - to MaxRow
    For C=1 To MaxCol   ' Changed
      On Error Goto SkipCell ' Assume we can't handle this cell
      Cells(R,C).Select
      S=Selection.Formula
      If Left(S,1)<>"=" And Len(S)>1 Then HtAdjust
Skipped:   ' New - target for resume below
    Next C
  Next R
 
  ' everything below here is new
  Exit Sub
 
SkipCell:
  Resume Skipped
End Sub
-----------------------------

What appears to have happened is that, either the cell selection was wrong (possibly because of the merged cells), or that the data in one of the cells was not compatable with a "string" variable (designed for text).  Since I don't have your exact data, i don't know which is the problem, but this version should work (I hope).
0
 

Author Comment

by:suzyQ
ID: 2638306
The macros run without error now, but they don't work. But thanks for trying.

The solution I need (if one exists) will increase row height to accommodate wrapped text, when row height and column width have already been changed from the defaults, and when rows contain some merged cells. The MS workaround works on rows with unmerged cells that have wrapped text, but not on rows with merged cells.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:suzyQ
ID: 2638309
P.S. The data in the cells is normal text. (ie. Format | Cells | Number | General)
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2638500
SuzyQ,

From your comments I believe to understand, your spreadsheet only contains merged cells across columns, not across rows ? Is that correct (I mean you merge e.g. 5 cells in the same row, 5 adjacent columns, into one cell).

Calacuccia
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2638516
IN that case, first run this macro, to transform all merged cells to center_Across_selection, without loosing the actual formatting:
The second part of the macro, will autofit all the rows. Before testing this macro, save a copy of the original spreadsheet under another name,and run this macro on the new spreadsheet.
The macro will automatically handle the complete spreadsheet. To enter the macro, hit Alt+F11, right-click on VBProject (MyFile) and select Insert/Module and insert this code in the right module window. Then close VBA, and launch the macro, from the to be treated spreadsheet, by using Alt+F11 and select the TransfromMergeToCenter macro, hit 'Run' button.

Sub TransformMergeToCenter()
For Each c In ActiveSheet.UsedRange
If c.MergeCells Then
c.Select
c.MergeCells = False
Selection.HorizontalAlignment = xlCenterAcrossSelection
End If
Next c
For Each r In ActiveSheet.UsedRange.Rows
r.AutoFit
Next r
End Sub

Hope this helps

Calacuccia
0
 

Author Comment

by:suzyQ
ID: 2640400
Thanks calacuccia. Your first comment assumed correctly that the merged cells are across columns, not rows. Unfortunately, not all of my merged cells are centered titles. Most of them are left justified "description" columns, otherwise I would have just unmerged and centered the titles across selection as cri suggested. But since you took the trouble to write the macro, I saved a copy of my ss and gave it a try. It works as you say it will, but it won't suit my ss because it centers columns that need to be left-justified. If I try to rejustify the text, it squeezes it all into one of the now-unmerged cells, instead of across the intended column width. It also has the unforeseen effect of adding border lines to my tables where the cells have become unmerged.

I think I'm going to have to bite the bullet on this one and redesign the ss to eliminate merged cells, so that I can use the MS support article workaround (autofit). It means that some of my tables will have wider columns than I want, but I have no other choice. I have to use this ss over and over for many different data sets, and I don't want to manually resize the rows one by one.

I'll leave this question open for the rest of the day, just in case there's anything else someone can suggest. If not, I'll accept cri's first comment as an answer.

Thanks for your help, everyone!
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2641016
Okay, here's one that works for all cases.

You need a second worksheet in the workbook that has *not* had its cells set to a specific height.  In my example here (tested on my Access 97), I use sheet1 and sheet2.  You need to change those references to the appropriate references for your sheets.  Read the comments to see where to change references

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

Sub HtAdjust()
'
'   Copies the text from the currently selected cell to a 'scratch' cell formatted to wrap text.
'   Get the row height, then format the original row with the needed height.

    Dim Ht As Single
    Dim CRow As Integer
    Dim CCol As Integer
     
    Dim Txt$
   
    With Selection             ' Optional - if textwrap is already set
        .WrapText = True       '   on your source worksheet, then
    End With                   '   remove this code

    Txt = Selection.Value
    CRow = Selection.Row
    CCol = Selection.Column
   
    ' Set up your scratch cells here.  You need to use a second worksheet for this.
    ' this example uses cell GZ:200
    Sheets("Sheet2").Select   ' change sheet name to appropriate name
    Cells(1, 1).Select        ' select a1
    With Selection
        .WrapText = True      ' make sure the textwrap property is on
    End With

    Selection.Value = Txt
    Rows("1:1").Select
    Ht = Selection.RowHeight  ' get the row height
    Cells(1, 1).Select
    Selection.ClearContents
    Sheets("Sheet1").Select   ' change sheet reference as needed
    Rows(CRow & ":" & CRow).Select
    If Selection.RowHeight < Ht Then Selection.RowHeight = Ht
    Cells(CRow, CCol).Select
End Sub

'Of course, you could go through and automate this to check all of your cells:

Sub AllCellAdjust()

  Const MaxRow = 5 ' set to your maximum rows in the spreadsheet
  Const MaxCol = 5 ' set to your maximum columns in the spreadsheet

  Dim R%, C%, S$

  For R = 1 To MaxRow  
    For C = 1 To MaxCol
      On Error GoTo SkipCell ' Assume we can't handle this cell
      Cells(R, C).Select
      S = Selection.Formula

      If Left(S, 1) <> "=" And Len(S) > 1 Then HtAdjust

Skipped:  
    Next C
  Next R
   
  Exit Sub
   
SkipCell:
  Resume Skipped
End Sub

0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2641042
Bhess,

You said "(tested on my Access 97)".

You cetainly meant Excel97 :-)
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2641056
Suzy,

Was the line height adjusted to your wishes with my last macro ?

In that case, try also running this little sub:

Sub AdjustRowHeights
For Each r In ActiveSheet.UsedRange.Rows
r.AutoFit
Next r
End Sub

(Once Again, save original version before you launch macros of this type)

Calacuccia
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2641071
Oooops - yes, Excel97.
0
 

Author Comment

by:suzyQ
ID: 2641604
calacuccia, yes the row height was properly adjusted with your previous macro. The problem was with the unwanted centering and with bordering of unmerged cells. I didn't try your latest macro because I've already established that autofit won't work.


bhess1, your macro did weird things to my ss, making row height much greater than it should be. I read through it and realized that the problem was that the height of the scratch cell was being set using the text from the original cell, but not taking the original cell's width into account. To complicate things, the original cells are not all the same width. So I took a stab at modifying the macro based on your example and I came up with this:


Sub HtAdjust()
'
'   Copies the text from the currently selected cell to a 'scratch' cell formatted to wrap text.
'   Get the row height, then format the original row with the needed height.
'
    Dim Ht As Single     ' height
    Dim Wt As Single     ' width
    Dim CRow As Integer
    Dim CCol As Integer

    Dim Txt$

    Wt = Selection.ColumnWidth
    Txt = Selection.Value
    CRow = Selection.Row
    CCol = Selection.Column

    ' Set up your scratch cells here.  You need to use a second worksheet for this.

    Sheets("Sheet 2").Select
    Cells(1, 1).Select        ' select a1
    With Selection
        .WrapText = True      ' make sure the textwrap property is on
    End With

    Selection.ColumnWidth = Wt
    Selection.Value = Txt
    Rows("1:1").Select
    Ht = Selection.RowHeight  ' get the row height
    Cells(1, 1).Select
    Selection.ClearContents
    Sheets("Sheet 1").Select
    Rows(CRow & ":" & CRow).Select
    If Selection.RowHeight < Ht Then Selection.RowHeight = Ht
    Cells(CRow, CCol).Select
End Sub


To my great surprise, my additions fixed this bug. However, your macros still don't fix my problem because they can't handle merged cells.

"On Error GoTo SkipCell" from the second macro is skipping cells that I need to height-adjust.

I appreciate the help, but I did say earlier that I need a solution which includes "when rows contain some merged cells". None of the proposed macros address this problem, so I am going to go with the workaround that I described in my last comment, which is based on information provided by cri.
0
 

Author Comment

by:suzyQ
ID: 2641612
Thanks cri, et al.
0
 
LVL 13

Expert Comment

by:cri
ID: 2641771
Thank you.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2642538
You're Welcome, SuzyQ.

I think that you will change the way you handle spreadsheets a little in the future :-)
0
 

Author Comment

by:suzyQ
ID: 2642853
Absolutely! It was a painful learning experience - the kind you tend not to forget...
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now