"Each Row from there to here"?

[Ed]

I'm trying to set my row height if it's less than a certain height. The
code worked fine on the whole sheet, but errors out when I try to constrain
it to my used range. At the "For Each rw" line, I get a syntax error:
compile error. When it was "In Worksheets("Sheet1").Rows", it worked fine,
but did all my blank rows and my header, too!

Suggestions are appreciated.
Ed

Sub TestRowHeight()

Dim FirstRow As Long
Dim LastRow As Long

FirstRow = 2
LastRow = Range("B65536").End(xlUp).Row

For Each rw = LastRow To FirstRow Step -1
If rw.RowHeight < 25.5 Then
rw.RowHeight = 25.5
End If
Next rw

End Sub

 

[Ed]

Paul, it works great in xl2000, too! (Needed an End If before Next rw.)

If I understand, I was trying to use rw as the object, instead as a property
of the Rows object? (Either way, I see I forgot to Dim it.)

Thanks much.
Ed

 

[pfsardella]

Sub TestRowHeight()

Dim rw As Long
Dim FirstRow As Long
Dim LastRow As Long

FirstRow = 2
LastRow = Range("B65536").End(xlUp).Row

For rw = LastRow To FirstRow Step -1
If Rows(rw).RowHeight < 25.5 Then Rows(rw).RowHeight = 25.5
Next rw

End Sub

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul

 

[pfsardella]

Ed,

This WebPage does a fairly decent job of explaining For..Next loops
and For Each .. Next loops.

rw is a counter. You are looping backwards through each row and
setting the RowHeight property.

http://msdn.microsoft.com/library/en-us/vbcon98/html/vbconloopstructures.asp

HTH
Paul

 

[Dave Peterson]

And if you really wanted to use a range object:

Option Explicit

Sub TestRowHeight()

Dim FirstRow As Long
Dim LastRow As Long
Dim rw As Range

FirstRow = 2
LastRow = Range("B65536").End(xlUp).Row

For Each rw In Range(FirstRow & ":" & LastRow).Rows
If rw.RowHeight < 25.5 Then
rw.RowHeight = 25.5
End If
Next rw

End Sub