VBA to Format Row Height

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I am new to VBA and I am having difficulty writing code that achieves
the following:

If the number of characters (text) in cell B4 >115 then adjust row B's
height to 30, else row B height remains 20.

I need to do this for rows B thru M (i.e., If M4>115 the Row M's
height =30)

I would greatly appreciate any suggestions.
 
Try this.

Sub wwRowHeight2030()
'for microsoft.public.excel "VBA to Format Row Height"
'Stephen Rasey - WiserWays, LLC, Houston, TX 040409 www.wiserways.com
'Select a range of cells.
'for each row of the selection, if col B of that row has > 115
characters,
' set row height to 30. Else set to 20.
'on return, give back the same range selection.

Dim rngSel As Range
Dim rngRow1 As Range
Set rngSel = Selection
For Each rngRow1 In rngSel.Rows
With rngRow1.EntireRow
.Select
If Len(.Range("B1")) > 115 Then
.RowHeight = 30 'range.height is read-only!
Else
.RowHeight = 20
End If
End With
Next
rngSel.Select

End Sub

<rant>
It took me too long to do this. I got tripped by range.height is a
read-only property. You must use range.RowHeight to change the height.
Yet MS Help for Height does not give a "See Also" link to RowHeight.

Stephen Rasey
WiserWays, LLC
Houston, TX
 
Stephen,

I appreciate the time and effort you put into this. It runs perfectly.

Thanks again,
Bob
 
Back
Top