suzyQ
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW: You can choose the wrapping _location_ with Alt+Enter, has the same function as Shift+Enter in Word
ASKER
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.
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.
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...)
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...)
ASKER
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!
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!
Do you happen to have merged cells ? If yes:
http://support.microsoft.com/support/kb/articles/Q157/0/97.asp
http://support.microsoft.com/support/kb/articles/Q157/0/97.asp
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
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
ASKER
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.
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.
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
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
ASKER
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?
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).
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).
ASKER
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.
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.
ASKER
P.S. The data in the cells is normal text. (ie. Format | Cells | Number | General)
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
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
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.HorizontalAlignm ent = xlCenterAcrossSelection
End If
Next c
For Each r In ActiveSheet.UsedRange.Rows
r.AutoFit
Next r
End Sub
Hope this helps
Calacuccia
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.HorizontalAlignm
End If
Next c
For Each r In ActiveSheet.UsedRange.Rows
r.AutoFit
Next r
End Sub
Hope this helps
Calacuccia
ASKER
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!
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!
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
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
Bhess,
You said "(tested on my Access 97)".
You cetainly meant Excel97 :-)
You said "(tested on my Access 97)".
You cetainly meant Excel97 :-)
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
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
Oooops - yes, Excel97.
ASKER
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.
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.
ASKER
Thanks cri, et al.
Thank you.
You're Welcome, SuzyQ.
I think that you will change the way you handle spreadsheets a little in the future :-)
I think that you will change the way you handle spreadsheets a little in the future :-)
ASKER
Absolutely! It was a painful learning experience - the kind you tend not to forget...
StartButton|Run excel /regserver (there is a blank before the switch)