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.