setting search range from VBA

[Ian]

I have the following to find the last cell used


myrow = range("sheet1!S65536").END(xlup).row + 1



I then need to search in cells based on the last cell found




or each cell in range(z3:zx") 'x = myrow above

if len(trim(cell.value)) < 1 then count = count + 1

next cell

range("a1").value = count


How can i pass the myrow value so i can make it my range for search
from z3 to z(myrow) ?

thanks,

 

[Guest]

set a range equal to S3 to S?? and offset it by 7 columns. Then you can just
move through the range...

Dim myRange As Range
Dim rng As Range

With Sheets("Sheet1")
Set myRange = .Range(.Range("S3"), _
.Cells(Rows.Count,
"S").End(xlUp)).Offset(0, 7)
End With

For Each rng In myRange
'do your stuff here...
MsgBox rng.Value
Next rng