Rolling Average when Unhiding Cells

I have a table in Cells A4:X50. I will hide the cells that are not relevant (based on date).

I would like it to calculate an average on the cells that are showing and have it change when I unhide more cells.

The pink cells are the ones I would like to calculate an average on.

running average help.xlsx

Sorry the cells actually go through BK50 when unhidden.

No one has any ideas?

Hi dianaschar

Because your file includes links to external files, the data is unusable. However, I have written the following macro which will do what you want.

Please Login or Register  to view this content.

To enter the code Press Ctrl+F11. Insert. Module. Coyp the above code there. In order to run it, press Alt+F8 and run YTDaverage.

You will need to change the type of file to a macro enabled file (ending ".xlsm") in order to save it.

Let me know if it works OK for you.

Regards
Alastair

That worked! Is there a way I can make it less relative to active cell though and have it work no matter where the active cell is?

erm... No.

That is to say that it is not depend on where you are when you run it.

The first line of the macro starts the routine from BL4, no matter what cell is active.

If you check the first line and are still having problems, let me know what is happening and I will try to sort it.

Regards
Alastair

Oh gotcha, nevermind.

Thank you very much.

Glad to have been able to help, but do I detect that there is something else that can be improved?

Regards
Alastair