Hi All. My first post here after being a closet guest/lurker for years.

Let's get this out of the way first: - What I know about VBA is gained from recording macros and then knife and forking bits and pieces from forums to end up with a result that I'm happy with. I.e. I know enough to be dangerous!

Anyway, I have come across a challenge that I cannot get around because the Macro recording doesn't capture what I do! I think it needs some kind of VBA string manipulation or something, but if I explain what I do manually (and which does not get recorded in a macro) perhaps some kind person(s) can help me with a solution. Thanks.

I have a number of named ranges which use non-contiguous cells, and I want to update a chart with the correct named range when some data changes. Examples of names and ranges:

GradGraphE4ALL "=Graphs!$B$10:$G$10,Graphs!$B$14:$G$14,Graphs!$B$18:$G$18,Graphs!$B$22:$G$22,Graphs!$B$26:$G$26"

GradGraphE4R1 "=Graphs!$B$10:$G$10,Graphs!$B$11:$G$11,Graphs!$B$15:$G$15,Graphs!$B$19:$G$19,Graphs!$B$23:$G$23"

GradGraphE4R2 "=Graphs!$B$10:$G$10,Graphs!$B$12:$G$12,Graphs!$B$16:$G$16,Graphs!$B$20:$G$20,Graphs!$B$24:$G$24"

GradGraphE4R3 "=Graphs!$B$10:$G$10,Graphs!$B$13:$G$13,Graphs!$B$17:$G$17,Graphs!$B$21:$G$21,Graphs!$B$25:$G$25"

GradGraphE5ALL "=Graphs!$B$10:$G$10,Graphs!$B$14:$G$14,Graphs!$B$18:$G$18,Graphs!$B$22:$G$22,Graphs!$B$26:$G$26,Graphs!$B$30:$G$30"

GradGraphE5R1 "=Graphs!$B$10:$G$10,Graphs!$B$11:$G$11,Graphs!$B$15:$G$15,Graphs!$B$19:$G$19,Graphs!$B$23:$G$23,Graphs!$B$27:$G$27"

GradGraphE5R2 "=Graphs!$B$10:$G$10,Graphs!$B$12:$G$12,Graphs!$B$16:$G$16,Graphs!$B$20:$G$20,Graphs!$B$24:$G$24,Graphs!$B$28:$G$28"

GradGraphE5R3 "=Graphs!$B$10:$G$10,Graphs!$B$13:$G$13,Graphs!$B$17:$G$17,Graphs!$B$21:$G$21,Graphs!$B$25:$G$25,Graphs!$B$29:$G$29"

GradGraphE6ALL "=Graphs!$B$10:$G$10,Graphs!$B$14:$G$14,Graphs!$B$18:$G$18,Graphs!$B$22:$G$22,Graphs!$B$26:$G$26,Graphs!$B$30:$G$30,Graphs!$B$34:$G$34"

GradGraphE6R1 "=Graphs!$B$10:$G$10,Graphs!$B$11:$G$11,Graphs!$B$15:$G$15,Graphs!$B$19:$G$19,Graphs!$B$23:$G$23,Graphs!$B$27:$G$27,Graphs!$B$31:$G$31"

GradGraphE6R2 "=Graphs!$B$10:$G$10,Graphs!$B$12:$G$12,Graphs!$B$16:$G$16,Graphs!$B$20:$G$20,Graphs!$B$24:$G$24,Graphs!$B$28:$G$28,Graphs!$B$32:$G$32"

GradGraphE6R3 "=Graphs!$B$10:$G$10,Graphs!$B$13:$G$13,Graphs!$B$17:$G$17,Graphs!$B$21:$G$21,Graphs!$B$25:$G$25,Graphs!$B$29:$G$29,Graphs!$B$33:$G$33"

I have a cell ("A1") on the sheet ("Graphs") in which the correct named range is displayed from a lookup table, and Cell ("B1") holds the non-contiguous range details, also using a lookup.

I have a chart ("Chart 17") where provided I just change the Data Source, I do not need to change the elements on either axis.

So, if I manually select the chart and right click to 'Select Data.." I can manually type in any of the named ranges above, say 'GradGraphE5R2', and 'OK', the chart will update with the correct values. If I opened up the 'Select Data..." form again, the "Chart data Range' field on form shows that the manually entered Name was expanded to the range as shown above.

However, if I record the steps above to enter a name for the range, all I get is the following VBA:

```
Sub Update_Graphs()
'
' Update_Graphs Macro
'
'
ActiveSheet.ChartObjects("Chart 17").Activate
End Sub
```

So it looks like the Macro is unable to capture that I typed a Name into the Data Source (even though it is accepted and expanded by the form).

If I repeat the above and paste in the actual Range of cells instead of the Name (which was resolved OK previously)

"=Graphs!$B$10:$G$10,Graphs!$B$12:$G$12,Graphs!$B$16:$G$16,Graphs!$B$20:$G$20,Graphs!$B$24:$G$24,Graphs!$B$28:$G$28"

I get this code in the Macro

```
Sub Update_graphs_2()
'
' Update_graphs_2 Macro
'
'
ActiveSheet.ChartObjects("Chart 17").Activate
ActiveChart.SetSourceData Source:=Range( _
"B10:G10,B12:G12,B16:G16,B20:G20,B24:G24,B28:G28")
End Sub
```

Display More
So what I need, I think, is for some VBA code for Chart 17 to read either the Named Range in "A1" and then retrieve the non-contiguous cells, or to read the "B1" cell (both A1 and B1 are lookup formuale) , and then set the Chart's Data Source to the associated non-contiguous range values.

I'm sure this will be a piece of cake for someone!

Thanks again in advance.