Macro to move chart range each time one row down

**Kinna** · 03-02-2008, 12:26 AM

Hi,

I need a macro to update chart ranges every month, the concept is simple, I need to show in my chart always the last 3 months, but I can't figure how to do that kind of macro to move the range down on my table.

I tryied to record something with the macro recorder, but all it does is move the range down from rows 2, 3 and 4 to rows 3, 4 and 5.
Next month I'll need to move from 3, 4 and 5 to 4, 5 and 6.

HTML Code:

**sweep** · 03-02-2008, 04:39 AM

Hi,

This can be done with dynamic named ranges, without the need for a macro. In the attached book, ranges are defined using

last3data=OFFSET(Plan1!$A$1,COUNT(Plan1!$A:$A)-3,0,3,1) and
last3months=OFFSET(Plan1!$B$1,COUNTA(Plan1!$B:$B)-3,0,3,1)

these are then used as the graph source data.

You can access named ranges via View > Names > Define

**Kinna** · 03-02-2008, 11:11 AM

It's working thanks!

Just one more doubt, what if I have my data in rows instead of columns, how should I write this formula in the names referrence?

---A---B---C---D
1 jan feb mar
2 3 5 1

Thank you very much for all your help.

**eddwright7** · 04-22-2008, 06:58 AM

Hi everyone

Using the answer above I can create dynamic ranges when data is arranged in columns, can anyone give an example with data arranged over rows:
So chart captures last 3 months for example.

Jan Feb March April
8 7 9 5

Any help would be really appreciated.

Ed