Offset

[Alex]

I am trying (within a macro) to create a subset range
that only includes the last 36 values of my input range.
I think that using the offset function is the way to do
this but am not sure.

For example, if the function is =newfunc(A1:A250), then
I want the subset to be the last 36 values. So if every
cell has a value in it then the subset would refer to
A215:A250. However, if the last cell with a value in it
was A172, then the subset would be cells A137:A172.

Thanks for the help.

 

[Frank Kabel]

Hi
if you have no blank rows in between try something like
sub foo()
dim lastrow as long
Dim firstrow as long
Dim rng as range

with activesheet
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
firstrow =application.max(lastrow-11,1)
set rng = .range("A" & firstrow & ":A" & lastrow)
msgbox rng.address
end with
end sub