Autofit - But At Least a Certain Height

[Susan]

In my spreadsheet, I have my row height set at 40 because that way everything
displays nicely when I print it out on letterhead (landscape). Is there a
way for me to adjust my cell height to make sure I have a height of at least
40, but allow it to adjust automatically for longer entries?

 

[Dave Peterson]

You could use a macro that does the autofit and then compares the results to
40. If it's smaller, then it'll resize the rowheight.

Option Explicit
Sub testme01()
With Worksheets("Sheet1")
.Rows(1).AutoFit
If .Rows(1).RowHeight < 40 Then
.Rows(1).RowHeight = 40
End If
End With
End Sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Susan]

Dave:

When I tried to run that macro, I received the following error:

Run time error "9"
Subscript out of range

Any suggestions? Thank you.

 

[Susan]

NEVERMIND DAVE IT WORKED!!!!! Thank you so much for your help.