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" wrote in message
...



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?