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