automatically selecting cell ranges for a chart

Posted by Eddie Adams on January 02, 2002 5:56 PM

Hi,
I was looking for some info on Excel that can help solve a little problem I have and hope you can help.
I have written a program that transfers some data into an Excel file with pre-existing charts. The number of columns that comprise each chart is fixed; however, the number of rows can change considerably. Therefore, I'm trying to find a way to expand or contract the range of cells for a chart (depending on the number of data rows) without the user having to do anything. Maybe I can invoke a macro or something, but I don't know much about them or how to send a variable to the macro (i.e., a variable indicating as cell range) for it to use. I'm trying to do this because new data will be exported into this Excel file many times and on daily basis, and I don't want the user to have to mess with the cell ranges that comprise the chart. Any help?

Thanks in advance!
E.A.

Posted by Adam S. on January 02, 2002 9:25 PM

a work-around approach

Hiya,

I have a simple (probably common) approach to this -assuming I've understood what you've got and what you're going for.

You have an existing file that has at least one chart based on a fixed range of data. Periodically you overwrite that data (not necessarily the same number or rows) and you want the chart to automatically reflect the new data. -hopefully I got a clear reading here.

Basically, start by setting your chart series to pull data from what you'd guess more than enough rows (if you expect a max of about 40-50 rows of data, chart through row 75 for example).

Insert a column directly to the left (or right) of the data range. Hard code numbers down the list and give it some header (eg. column header name: order, data 1:75).

Then apply an autofilter to the data and filter an appropriate column to hide the blank cells in that range (>0 usually is fine).

In addition to hiding the extra blanks, it also hides these values on the chart.

It sounds like you already have a macro in place that basically overwrites the chart data. You could tweak it further by first toggling off the existing filter, then overwrite the data, then reapply the filter. should be pretty easy as long as your chart data is on its own sheet.

Hope that helps,
Adam S.

Posted by Bariloche on January 02, 2002 10:43 PM

some more approaches

Eddie

If you have VBA programming skills, record a macro of creating a chart so that you can see the objects and methods involved, then write a SheetActivate event macro that changes the appropriate range when the chart sheet is activated.

Or, if your data is suitable for use in a pivot table you could create a PivotChart. The data range houskeeping would be taken care of, but PivotCharts have their own idiosyncracies that may be worse (i.e., more annoying) than what you're dealing with now.

enjoy

Posted by Ivan F Moala on January 02, 2002 11:36 PM

Re: some more approaches

Eddie
Yet another option

Use dynamic named ranges
In the worksheet's Insert menu choose Names > Define
Enter a name for your 1st range, such as myrg1. Then, in the Refers to: box, enter the following formula:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

where Sheet1!$A$1 is your start row...change as required.

Do this for the number of data rows required
eg MyRg2, MyRg3 etc

Then in your chart reference the series source
data Values as;

='Myworkbookname.xls'!MyRg1

Adding to the Data row (In this case from A1 down)
will dynamically change the chart.

HTH

Ivan

Posted by Eddie Adams on January 04, 2002 9:04 AM

Thanks for your suggestions. They helped a lot.
E.A.