Put all of the following code in a Module (Insert/Module from the VB
editor's menu bar). Change the Const statements in the FitText macro to the
minimum and maximum number of pixels high you want to limit a row to. After
you have done that, go back to the worksheet, select a cell with text in it
and run the macro... it will keep the height within the bounds you set while
changing the row height of the selected cell to accommodate the text (column
width remains fixed).

Private Declare Function GetDC Lib "user32" _
(ByVal hwnd As Long) As Long

Private Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hDC As Long, _
ByVal nIndex As Long) As Long

Private Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, _
ByVal hDC As Long) As Long

Private Const LOGPIXELSX = 88 'Pixels/inch in X
Private Const POINTS_PER_INCH As Long = 72

Private Function PointsPerPixel() As Double
Dim hDC As Long
Dim lDotsPerInch As Long
hDC = GetDC(0)
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
PointsPerPixel = POINTS_PER_INCH / lDotsPerInch
ReleaseDC 0, hDC
End Function

Public Sub FitText()
Dim PPP As Double
Const MinPixelsHigh As Long = 17
Const MaxPixelsHigh As Long = 85
PPP = PointsPerPixel
With ActiveCell
.Rows.AutoFit
If .RowHeight < MinPixelsHigh * PPP Then
.RowHeight = MinPixelsHigh * PPP
ElseIf .RowHeight MaxPixelsHigh * PPP Then
.RowHeight = MaxPixelsHigh * PPP
End If
End With
End Sub

--
Rick (MVP - Excel)


"Bert" wrote in message
...
For Excel 2003, is there a way to determine the number of lines of text in
a cell (with wordwrap turned on? (Similar to LineCount for a textbox.)
I'm adding blocks of text to a cell, and--within limits--want to be able
to resize the cell so the whole block will be visible. The text may or
may not contain CR/LF characters, so there could be blank lines I'd like
to have displayed and included in the line count.
I'd prefer not to use autofit, so I can maintain a minimum rowheight, as
well as keeping from going over a maximum rowheight.
Thanks.
Bert