Offset
 

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.

Offset
 

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


--
Regards
Frank Kabel
Frankfurt, Germany


Alex wrote:
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.