Format RowHeight question

[ChipButtyMan]

Hi,
I have text in a column of rows that is 9 point Lucida Console.
Some of the cells in the column contain several rows of text.
I want to format the row height something like ;

For i = 1 To 1000
If Range("B" & i) = Four Rows Of Text Then
RowHeight = 95
If Range("B" & i) = Three Rows Of Text Then
RowHeight = 85
End If
End If
Next i

Any help is very much appreciated.

 

[Barb Reinhardt]

Why not use

Range("B" & i).Rows.AutoFit

 

[Rick Rothstein]

Give this code a try...

Sub SetRowHeight()
Dim i As Long
Dim CellVal As String
For i = 1 To 1000
CellVal = Cells(i, "B").Value
Select Case Len(CellVal) - Len(Replace(CellVal, vbLf, ""))
Case 3 ' Four lines have three Line Feeds
Rows(i).RowHeight = 95
Case 4 ' Five lines have four Line Feeds
Rows(i).RowHeight = 85
End Select
Next
End Sub

Note that the RowHeight cannot be set to any arbitrary number... the values
it ultimately rounds to depends on the font (has to do with the font needing
to occupy an exact number of pixels), so you may not get exactly 85 and 95
points in height when done.

 

[ChipButtyMan]

Thank you Barb & Rick,
both solutions were fine for me but
Rick's solution provided the fine tuning I need.
Thank you both for your time & expertise.