Dynamic Range that moves

[learning_vba]

Hello Folks,

I tried searching the forums, but did not find exactly what I was looking for (hopefully my search skills dont stink). Anyway, here is my scenario I hope you kind people can help with. I am putting together a stock return spreadsheet, and I am using dynamic ranges for the returns, because I will be adding data on a daily basis. Using the following formulas is how I am dynamically naming the ranges:

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

So I have my stock return data listed chronologically, with the newest data being added daily to the bottom of the range. What I would like to be able to do is create a secondary dynamic named range that will only include the last 30 days stock return data. That is to say, assume A1:A200 has the data. Tomorrow I will add the new data in cell A201. I would like a second dynamic range that will cover A171:A201, and the next day the range would be A172:A202 and so on. Is this possible?

I hope this makes sense to everyone. Thank you so much for your help.

Regards

[arthurbr]

Hi,

the range you defined is always 199 rows long and one column wide
If you need a dynamic range use counta(a:a) which will count all non empty cells in col A.
Is there a special reason why it is only 1 column wide ?

For your second question, do you only add 1 row each time the range is adapted, or could there be more?

[learning_vba]

Hi,

the range you defined is always 199 rows long and one column wide
If you need a dynamic range use counta(a:a) which will count all non empty cells in col A.
Is there a special reason why it is only 1 column wide ?

For your second question, do you only add 1 row each time the range is adapted, or could there be more?

Hello arthurbr,

Thank you for your response, the formula I listed was only an example. I actually have it set to go down through 5000, just to make sure I dont have to update the formula ever. As for it being only one column wide- yes, I am only using the % return data for this, nothing else. Hope this helps.

[arthurbr]

If you only need the last 30 last rows of your column try =OFFSET($A$2,count(a:a)-29,0,30,1)

[learning_vba]

If you only need the last 30 last rows of your column try =OFFSET($A$2,count(a:a)-31,0,30,1)

arthubr,

Thank you so much for your help.

Regards

[arthurbr]

I changed the 31 to 29