Autofit makes rows too tall

L

leaftye

leaftye said:
When I have multiple lines in a cell, Autofit makes my rows too tall no
matter how I activate the Autofit utility. What can I do so Autofit
sizes my rows properly?

Eugene

Here's what seems to be working for me:


Code:
--------------------

Sub TrimRightSpaces()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
'Modified to only trim right spaces
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.RTrim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Sub RowHeight()
Dim OriginalWidth As Integer
Dim OriginalHeight As Integer
Dim NewHeight As Integer

Application.ScreenUpdating = False

'Eliminate white space so this function can work properly
TrimRightSpaces

'Excel likes it when you change width back and forth
'before setting the height....
With ActiveCell
OriginalWidth = .ColumnWidth
OriginalHeight = .RowHeight
.EntireColumn.AutoFit
.ColumnWidth = 47
With .EntireRow
.AutoFit
NewHeight = .RowHeight
End With
.RowHeight = NewHeight '+ 15
.ColumnWidth = OriginalWidth
End With

Application.ScreenUpdating = True
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top