Excel VBA variable Chart Range

Question

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

teylyn · Accepted Answer · 2018-06-15 07:49:18Z

1

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

answered Jun 15, 2018 at 7:49

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

7660 Over a year ago

Thank you for your fast response, but it i dont want to run it on a phone or online so Vba is also a solution for me ;) there are a few more macros in this file this is justone of them which doesnt work

John F · Accepted Answer · 2018-06-15 10:15:31Z

0

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.

answered Jun 15, 2018 at 10:15

John F

2761 silver badge6 bronze badges

Comments

Vityata · Accepted Answer · 2018-06-15 10:19:30Z

0

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.

answered Jun 15, 2018 at 10:19

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Comments

m-sarabi · Accepted Answer · 2024-09-25 00:11:55Z

0

I found my own solution:

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

edited Sep 25, 2024 at 0:11

m-sarabi

2,3101 gold badge18 silver badges25 bronze badges

answered Jun 15, 2018 at 10:55

7660

1073 silver badges6 bronze badges

Collectives™ on Stack Overflow

Excel VBA variable Chart Range

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related