Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 766
  • Last Modified:

Excel 97 - wrapping text in cells

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
suzyQ
Asked:
suzyQ
  • 10
  • 7
  • 5
  • +1
1 Solution
 
criCommented:
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
 
criCommented:
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
 
criCommented:
BTW: You can choose the wrapping _location_ with Alt+Enter, has the same function as Shift+Enter in Word
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
suzyQAuthor Commented:
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
 
criCommented:
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
 
suzyQAuthor Commented:
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
 
criCommented:
Do you happen to have merged cells ? If yes:
http://support.microsoft.com/support/kb/articles/Q157/0/97.asp
0
 
criCommented:
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
 
suzyQAuthor Commented:
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
 
Brendt HessSenior DBACommented:
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
 
suzyQAuthor Commented:
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
 
Brendt HessSenior DBACommented:
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
 
suzyQAuthor Commented:
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
 
suzyQAuthor Commented:
P.S. The data in the cells is normal text. (ie. Format | Cells | Number | General)
0
 
calacucciaCommented:
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
 
calacucciaCommented:
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
 
suzyQAuthor Commented:
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
 
Brendt HessSenior DBACommented:
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
 
calacucciaCommented:
Bhess,

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

You cetainly meant Excel97 :-)
0
 
calacucciaCommented:
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
 
Brendt HessSenior DBACommented:
Oooops - yes, Excel97.
0
 
suzyQAuthor Commented:
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
 
suzyQAuthor Commented:
Thanks cri, et al.
0
 
criCommented:
Thank you.
0
 
calacucciaCommented:
You're Welcome, SuzyQ.

I think that you will change the way you handle spreadsheets a little in the future :-)
0
 
suzyQAuthor Commented:
Absolutely! It was a painful learning experience - the kind you tend not to forget...
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 10
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now