troubleshooting Question

Change chart range by VBA

Avatar of Arundo
Arundo asked on
Microsoft Excel
15 Comments1 Solution810 ViewsLast Modified:

I am struggled with a way to update my charts. This is how I would like it is working.
Chart data is located in a row, so evry day new column with data is added.  I have multiple charts each of them has several series. I would like a macro to extract formula of series collection, which is, for example SERIES(Sheet1!$A$11:$C$11,Sheet1!$E$1:$EN$1,Sheet1!$E$11:$EN$11,1) and replace column EN (can be also a different column) by last column in the row with a help of End(xlToRight)). So that formula becomes (Sheet1!$A$11:$C$11,Sheet1!$E$1:$EZ$1,Sheet1!$E$11:$EZ$11,1) .

Sub Charts()
Dim m As ChartObject
Dim n As ChartObject
For m =1 to 6
For n = 1 To Sheet1.ChartObjects(m).Chart.SeriesCollection.Count
Sheet1.ChartObjects(m).Chart.SeriesCollection(n).Formula = ....
Next n
Next m
End Sub

!!! Please do not offer dynamical named ranges based on offset function, because I have several sheets with multiple graphs each consisting of various series. Time killing to put in all named series.
Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros