setting search range from VBA
 

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,

setting search range from VBA
 

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

--
HTH...

Jim Thomlinson


"Ian" wrote:

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,