how to select range when no. of rows depends from text place ??

[Palka]

How to find text in worksheet and get the address of this cell ?

My example:
A B C D
1 x c
2 x c
3 x c
4 x c
5 y
I'm interested to select range "A1":"XY"
where the XY should be always before the y, so I need to find a text
"y" and read the address of this cell and passed it to
Range("A1":XY).select
in this case it will be range ("A1":"B4")

but below 1 - will be ("A1":"B3") ; 2 - will be ("A5":"B9")
A B C D
1 x c
2 x c
3 x c
4 y
5 x c
6 x c
7 x c
8 x c
9 x c
10 y

 

[Tom Ogilvy]

sub SelectRanges
dim i as long
Dim rng as Range
Dim start as Range
set start = Range("A1")
do while cell(i,1) <> ""
if lcase(cells(i,2)) = "y" then
set rng = range(Start, cells(i-1,2))
rng.select
set start = cells(i-1,1)
end if
i = i + 1
Loop
' select the last
set rng = rng(start,cells(i-1,2))
rng.Select
End sub