Move Chart series data via VBA

Question

I have the following code

Dim ss As Series
Dim strs() As String
Set ss = ActiveChart.SeriesCollection(1)
strs = Split(ss.Formula, ",")
Dim rg As Range
Set rg = Range(strs(2))
Set rg = rg.Resize(rg.Rows.Count + 1)
ActiveChart.SeriesCollection(1).Values = rg

This allows me to add 1 more item of data to my data series but I want it to also remove the 1st entry every time I run it. For example Day 1- range is A1 to C1 then I run the macro I want it to go to B1 to D1

I hope this makes sense

David Zemens · Accepted Answer · 2015-02-04 17:13:39Z

2

This will "move" the range, rather than resizing it, which is what you're doing.

Set rg = rg.Offset(1,0)

answered Feb 4, 2015 at 17:13

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

8 Comments

4d554d424c4553 Over a year ago

Thanks @David Zemens for that but how can I get it to move the Category at the same time?

David Zemens Over a year ago

The series also has an .XValues property, which sets the categories. Use that to assign the category labels. Note: this method is kind of clunky, you're assigning literal values rather than linking to the cells on the worksheet, so your chart will not "update" if the data changes.

4d554d424c4553 Over a year ago

Can I ask how you would suggest is the best method to do this?

David Zemens Over a year ago

There are literally thousands of examples of manipulating Excel charts all over the interweb. You might be able to use the .SetSourceData method (here).

Jon Peltier Over a year ago

@DavidZemens - If you assign a range to .Values or .XValues, you are not assigning literal values, you are in fact assigning the range. You do however need to use a formula to assign a range to the .Name of a series. But use ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("C2").Address(External:=True) and let Excel figure out whether the sheet name requires single quotes etc.

|

Collectives™ on Stack Overflow

Move Chart series data via VBA

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related