Dynamic Chart Range

[Chuck Taylor]

I'm trying to dynamically change the data range for a line chart
either in the chart setup or through VBA.

I first tried via the chart wizard - I defined a range name and by
using an OFFSET function in the "refers to" made it dynamic. I
couldn't figure out how to get the wizard to accept a range name
rather than a set data range.

I've done dynamic data for pivot tables in VBA so I tried that. For
pivot tables, I can make those dynamic by using "name.add" and then
referring to a string in a cell that I've built that defines the range
boundaries. Not that elegant and looking for something simplier if
possible.

In VBA, I also tried setting a range (ie, SET rng1 =
range(cells(1,1),cells(i,1)) then in the activechart syntax tried to
feed it range:= rng1, etc. but couldn't get that to work either.

I don't want to define the chart range as "$A$2:$A$65536" and use
"#N/A" in the blank fields to trick the graph into not showing the
data - not that elegant either.

Any ideas? Thanks.

 

[Piranha]

Chuck,
See if there is anything here that can help you:

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/

http://peltiertech.com/Excel/Charts/Dynamics.html

Does that help?
Dave

 

[Guest]

I'll asume that you created the dynamic named range through
Insert>Name>Define and used the "Refers to" window in this dialog. To ensure
that it is working correctly, I usually click inside the "Refers to" window
and check to see that it highlights the correct range.

Assuming the range name is "MyData", in the Series tab of the Chart Wizard,
define the values for the Line chart as follows:

=XYZ.xls!MyData

where XYZ.xls is the name of the workbook.

To create a dynamic named range with VBA try this:

Dim Nm As Name
Dim txt As String
txt = "=Offset(Sheet2!$C$1, 0, 0, Count(Sheet2!$C:$C), 1)"
Set Nm = ThisWorkbook.Names.Add("MyData", txt)

Regards,
Greg