make the table at AA7 a dynamic range and set the chart's data to the range

Insert / Name / define

Name: ChartData

=OFFSET(Sheet1!$AA$7,0,0,COUNTA(Sheet1!$AA$7:$AA$3 7))


So it AA7 and the next three rows are A,B,C,D


then =ROWS(ChartData) has teh value 4

if I add E,F G to the next three rows ( AA13:= G) the formual shows 7


check this out on www.cpearson.com






"LiAD" wrote:

Hi,

I have a macro which is taking data from one region on a sheet, (EB7-FK31),
then copy pasting the values to another region (AA7) and then deleting the
empty rows.

From the reduced table I would like to plot a chart of the data, without a
lot of empty rows if possible. So there can be 1 to 23 rows to plot.

Which is the easiest way to achieve this?

- Have a chart on the page which gets updated to the new range every time
data is added, or,
- A macro that deletes the current chart and then replots a new one to the
nex range

For info when setting the chart properties I would like to have presets for
title fonts, line colours etc.

Thanks for your help
LiAD