Excel VBA variable Chart Range

0
votes

I have a datatable were i add every week a column and i tryed to write macro to change the chart source. 

Sub test()
Dim ges, koz, daz  As Range
Dim Diaz As Integer

Sheets("Autopilot").Select
Diaz = Range("I2").Value  (the value will change every week like I2 = I2 +1)
Set koz = Range("C3").Resize(, Diaz)   ( this is the Header row)
Set daz = Range("C772").Resize(2, Diaz)  (these are the 2 data rows for the chart) 
Set ges = Union(koz, daz)
Sheets("Diagramm").Select
   ActiveChart.ChartArea.Select
   ActiveChart.SetSourceData Source:=Sheets("Daten").Range(ges)
End Sub

the last row doesn't work. i tried it without the Range at the end (only .ges).

anyone has an idea how this would work?

best regards

4
excelvba

4 Answers

1
votes

There is no need for VBA to create a dynamic chart range. It can easily be done with formulas in range names. Take a look at Jon Peltier's site, especially how to chart the last x rows of a data set that is growing all the time.

No need to run code, just formulas. This has the added benefit that it works in Excel online and on a phone, whereas VBA won't.

https://peltiertech.com/Excel/Charts/DynamicLast12.html

0
votes

Teylyn's answer using dynamic defined names will work well for you, or if you don't want to do formulas, you can convert the range of cells into an Excel Table (INSERT...TABLE or Crtl T). Make your chart based on that table and any data you append to the bottom of the table will automatically be incorporated into the chart. Again no need for VBA.

0
votes

This does not work: ActiveChart.SetSourceData Source:=Sheets("Daten").Range(ges)

Because ges has Sheets("Autopilot") as Parent worksheet. Thus, try either:

ActiveChart.SetSourceData Source:=Sheets("Autopilot").Range(ges)

or

ActiveChart.SetSourceData Source:=Sheets("Daten").Range(ges.Address)

depending on what you need.

0
votes

thank you all for your help, i found my own solution:

With Sheets("Daten") ActiveChart.SetSourceData Source:=ges End With

best regards