VBA - Named ranges in an excel chart

Hi there,

I'm no programming expert but I have a basic understanding of VBA.

What I'm trying to achieve is essentially a dynamic chart. I have created a drop down menu which will select data based on the value of the drop down menu.

The data is being pulled from cells, which is currently in laid out like a table with category down the side, and time periods across the top. The chart should contain all categories, but then the periods displayed in the chart needs to be dependent on the selection from the drop down menu.

I know how to hard core this, for example:
If the user selects the time period '2000' then:

Please Login or Register  to view this content.

if the user selects the time period '2001' then:

Please Login or Register  to view this content.

The above works fine, but the problem is these tables often moved and columns are always removed / added. I've written some code to identify the range I need and store it in a name range.

I then tried the following:

Please Login or Register  to view this content.

'category refers '$B$10:$B$19' and timeperiods refers to 'Q$10:$AD$19'.

The problem is, it selects everything between B10 and AD19 so column C, D, E... etc are all showing in the chart.

Is there any way, using the named ranges that I listed above to have the chart only show the columns in the ranges?

I greatly appreciate any help that anyone can offer on this as I've been stumped on it for some time!

Thanks in advance.

Ash

Use Union.
Depending on what category and timeperiods are (it was not really clear for me):
a) named ranges (Ctrl+F3 in workbook):

Please Login or Register  to view this content.

b) variables of range type (dim category as Range ....)

Please Login or Register  to view this content.