Excel rows with text are too tall

[KCWong]

Here are the details of my problem:

I create a csv file. One of the columns has cells with a lot of text. So I format the cells by choosing the column and hitting Format -> Cells-> Alignment -> Wrap text. Almost invariably, some rows expand to like 3" tall, but only have 2 lines of text. The column is typically about 3" wide. I can send any interested parties a sample file.

How do I get the rows to size properly? I could set them all to the same height, but some rows will have 10 lines of text, and some will have 1. I want them to size according to how much text is in there.

 

[foundryqa]

kcwong1,

Is there a chance that there are line return characters or spaces that are filling up the rest of the three inches?

What happens when you highlight anything after the second word or before the first in the large cell, do areas highlight indicating additional characters?

Just a few ideas,

Fred

 

[KCWong]

Hi Fred:

I've thought of that. But I've noticed that SOMETIMES it happens, and sometimes it doesn't. So if there were extra spaces, you'd think that it would happen all the time, no matter what? It's only under certain conditions that this happens, I suspect and I want to know what those conditions might be. I have noticed that closing and reopening the spreadsheet SOMETIMES helps (odd as that sounds), or hitting the Enter key after the entry. But not always. That would tend to disprove the extra characters thing though.

Thanks for reading and replying though!

 

[PeterMoran]

Hi kcwong1,

I have struggled with this situation myself on Excel 2000.

It seemed to me that the Wrap formula that calculates the number of lines required, calculates an extra line when the last line is close to being full. Even after fixing the height, I agree the problem often returned.

The basic problem with fixing the lines is that you have to know the exact height in Excel's measurement (whatever that is!) and enter the correct number to fix the situation. For some reason Excel does not allow you to copy row heights or column widths from other rows or columns.

For what it is worth I was given two macros from the WOPR boaer (as below) and included them in my Menus for Format Row and Format Column. The first entry in the Row menu is Copy Height, and the first one in the Column menu is Copy Width. I use them frequently.

Sub AdjustColumnWidths()
' Ex Pieter Janssens on WOPR Excel board
' Copies Column Width from selected Cell
' Select Receiving Columns & then run Macro
' Loaded 27/6/03
'
Dim SelectedColumns As Range
Dim CurCol As Range
Dim SelectedColumnWidth As Single
Set SelectedColumns = Selection
On Error GoTo SubEnd
SelectedColumnWidth = _
Application.InputBox _
( _
Prompt:= _
"Select a Cell whose Width is to Apply to the Selected Column(s)", _
Title:="Apply ColumnWidth - Select Column", Type:=8 _
).ColumnWidth
For Each CurCol In SelectedColumns.Columns
CurCol.ColumnWidth = SelectedColumnWidth
Next
SubEnd:
End Sub

Sub AdjustRowHeights()
' Ex Pieter Janssens on WOPR Excel board
' Copies Row Heights from selected Cell
' Select Receiving Rows & then run Macro
' Loaded 27/6/03
'
Dim SelectedRows As Range
Dim CurRow As Range
Dim SelectedRowHeight As Single
Set SelectedRows = Selection
On Error GoTo SubEnd
SelectedRowHeight = _
Application.InputBox _
( _
Prompt:= _
"Select a Cell whose Height is to apply to the Selected Rows", _
Title:="Apply RowHeight - Select Row", Type:=8 _
).RowHeight
For Each CurRow In SelectedRows.Rows
CurRow.RowHeight = SelectedRowHeight
Next
SubEnd:
End Sub

The limitation is that you still have to do this separately for each different row height you have to handle, but it still saves time and is more convenient. I don't know how you would address doing something like this in a more automated fashion.

Also, if I have rows that are three rows high and I want them to be reduced to two rows high then I do the first one and then use F4 successfully on the remainder. Of course you could also select all the rows first and then run the macro.

Good Luck!
Peter Moran

 

[dcompto]

Would this do the trick?
Select all rows then
Format > Row > AutoFit