How do I modify the autofit function in Excel?

[Guest]

I have created spreadsheets, to list students and their data, that have to be
updated weekly. The lists have to be printed as well. To keep the width
within one page, the font has to stay at 8 pts. For ease of reading, each
record alternates color from its "neighbors". I have created macros to
reshuffle the colors after I resort names when new ones are added in, since
the color of the records follows the name to its new row. I have a value of
40 for the height of the rows that is mostly adequate. The problem comes from
a wrapping memo field within each record that may increase the height of the
row to above 40 pts. When I re-sort the list of names, the macros take care
of the colors, but the height of the rows stay put; that means that some
records that could use more space in the memo field get cropped off, while
some that do not need the space may now be located in a row that is too high.
The autofit function works for this problem, except that it "squishes" the
data, so that it becomes less easy to read. What I would like to do is to be
able to use the autofit function, but also to put a minimum of 40 pts height
per row. Is that possible?

 

[tony h]

After doing the autofit run this. This will add 40 to each row within
the loop. If you only want to extend those over a cerain size put in an
"if " to test the row height first.

Dim i As Integer
Dim rng As Range

Set rng = Range("1:1")
For i = 1 To 50
rng.Offset(i).RowHeight = rng.Offset(i).RowHeight + 40
Next


best wishes
Tony

 

[Dave Peterson]

I think that you'll have to loop through the rows and look at the rowheights:

Option Explicit
Sub testme()

Dim myRow As Range

With Worksheets("sheet1")
For Each myRow In .Range("a1:A99").EntireRow.Rows
myRow.AutoFit
If myRow.RowHeight < 40 Then
myRow.RowHeight = 40
End If
Next myRow
End With
End Sub