Selecting a range by using a loop
 

I am trying to select a range by using either a do until or do while loop

I have a value in colum "a" and need to loop through the collection till the
value in a is ="".

The range will be starting in eg B7 to g9 range("b7:b9").select
A7 wil start as the active cell so
The code will be something like this:
activecell.offset(-1,-7).select ' this will select a7 so the first value
is known
range(activecell.offset(1,0):activecell.offset(7," unknown").select
I need to know wht the unknow value is.
Now it must loop down till value = "" is established
How do i determin the 2nd value ie "b9"

Ie I do know the starting point of range, i do know the amount of colums in
the offset but not the amount of the rows, which is determind but the amount
of rows looped down


Thanks

Selecting a range by using a loop
 

Try this

Sub lasca()
Dim rng As Range
Dim i As Long
Set rng = Range("B7:B9")


For i = 1 To rng.Count
Do Until ActiveCell = ""
'your search value
'
Loop

Next i

End Sub

set rng

"lasca" wrote:

I am trying to select a range by using either a do until or do while loop

I have a value in colum "a" and need to loop through the collection till the
value in a is ="".

The range will be starting in eg B7 to g9 range("b7:b9").select
A7 wil start as the active cell so
The code will be something like this:
activecell.offset(-1,-7).select ' this will select a7 so the first value
is known
range(activecell.offset(1,0):activecell.offset(7," unknown").select
I need to know wht the unknow value is.
Now it must loop down till value = "" is established
How do i determin the 2nd value ie "b9"

Ie I do know the starting point of range, i do know the amount of colums in
the offset but not the amount of the rows, which is determind but the amount
of rows looped down


Thanks

Selecting a range by using a loop
 

Not sure what you are trying to do, but you can get to the last row using

Dim myRow As Long
myRow = Cells(Rows.Count,1).End(xlUp).Row

And then use it like

Dim myR As Range
Dim myCell As Range
Set myR = Range("A2:A" & myRow)

For Each myCell In myR

--
HTH,
Bernie
MS Excel MVP


"lasca" wrote in message
...
I am trying to select a range by using either a do until or do while loop

I have a value in colum "a" and need to loop through the collection till the
value in a is ="".

The range will be starting in eg B7 to g9 range("b7:b9").select
A7 wil start as the active cell so
The code will be something like this:
activecell.offset(-1,-7).select ' this will select a7 so the first value
is known
range(activecell.offset(1,0):activecell.offset(7," unknown").select
I need to know wht the unknow value is.
Now it must loop down till value = "" is established
How do i determin the 2nd value ie "b9"

Ie I do know the starting point of range, i do know the amount of colums in
the offset but not the amount of the rows, which is determind but the amount
of rows looped down


Thanks