Selecting range based on values

I want to select a range consisting of the rows in column B which contain a
certain value (5). These will all be adjacent to one another. Then extract
the corresponding values in column M in only these rows to be used for later
analysis.

#How can I extract these values into a single column in another worksheet?

Sub GetCells()
dim res as Variant, rng1 as range
dim lastrow as Long, rng as Range
res = Application.Match(5,Columns(2),0)
lastrow = res
do while cells(lastrow,2) = 5
lastrow = lastrow + 1
Loop
set rng = Range(cells(res,2),cells(lastrow - 1,2))
set rng1 = Intersect(columns(13),rng.EntireRow)

rng1.select
End Sub

rng1 will hold a reference to the cells in column M.

--
Regards,
Tom Ogilvy


"Alan M" <AlanM@discussions.microsoft.com> wrote in message
news:530A82BC-D6AC-4EDD-BA73-F27B3329575B@microsoft.com...
>
>
>
> I want to select a range consisting of the rows in column B which contain
a
> certain value (5). These will all be adjacent to one another. Then extract
> the corresponding values in column M in only these rows to be used for
later
> analysis.
>
> #How can I extract these values into a single column in another worksheet?