Selecting range based on values

[Guest]

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?

 

[Tom Ogilvy]

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.