Using VB to resize bar chart and change range - but it keeps switching x/y series

[CatyH]

Row labels A2:A4
Column headers B1:AE1
Data B2:AE4

The number of columns of data will be a parameter/variable.
When I need to use one column of data, the graph should draw with data B2:B4
When I need to use two columns of data, the graph should draw with data B2:C4... etc

I also want to resize the graph... so I made all this happen with CASE statements in VB.

Only problem... My original graph has the row labels (A2:A4) as the three categories for a clustered column chart... with the goal of having the columns clustered in those three groups.

But when I run the macro and the graph re-draws, it changes to making the _columns_ be the main clusters (e.g. if I have 7 columns of data, then I'll have 7 clusters, each with three columns) but what I want is to keep the three clusters and have the variable number of columns within each.

I've tried recording macros and inspecting the code to figure out where the graph properties exist... but there doesn't appear to be a way to force the x/y column/row designations....

Any help is GREATLY APPRECIATED! Thank you in advance!

 

[Jon Peltier]

Somewhere you need to set PlotBy to xlColumns, either here when you set the data:

ActiveChart.SetSourceData Source:=ActiveSheet.Range("A1:H4"), PlotBy:=xlColumns

or after the chart's data is changed:

ActiveChart.PlotBy:=xlColumns

 

[CatyH]

Somewhere you need to set PlotBy to xlColumns, either here when you set the data:

ActiveChart.SetSourceData Source:=ActiveSheet.Range("A1:H4"), PlotBy:=xlColumns

or after the chart's data is changed:

ActiveChart.PlotBy:=xlColumns

wow! thanks for the speedy reply! I recognize your name from MANY excel queries/pages! I'm honored! Can't wait to try this out... now I need to figure out how to strip out too many volatile formulas that are really bogging it down... but that's for another post!