[SOLVED] Excel VBA graph range that moves down when updated always showing 2 months of data

Excel VBA graph range that moves down when updated always showing 2 months of data

I have constructed Stacked cluster chart in excel with this method: http://www.tushar-mehta.com/excel/charts/stacked_columns.htm

Now I want the chart to be dynamic and show last two months of daily data but as I have blank rows it is difficult with offset or index or the name manager. Therefore, I am looking for a VBA code which would move down the area of the chart by one day when hitting a button.

The below mentioned code only moves column B (values of product 1) down by one cell but not column a where the dates are and column c where the values of product 2 are. I would very much appreciate the help. Thanks in advance.

Sub moveSelection()
 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.Offset(1, 0)

 ActiveChart.SeriesCollection(1).Values = rg

End Sub

Solution

You were fine, as far as you went. But you need to get the (shared) X values and the Y values from the second series.

Sub MoveSelection()
  Dim ss1 As Series, ss2 As Series
  Dim strs() As String
  Dim rgX As Range, rgY1 As Range, rgY2 As Range

  Set ss1 = ActiveChart.SeriesCollection(1)
  strs = Split(ss1.Formula, ",")
  Set rgX = Range(strs(1))
  Set rgY1 = Range(strs(2))

  strs = Split(ss2.Formula, ",")
  Set rgY2 = Range(strs(2))

  Set rgX = rgX.Offset(1)
  Set rgY1 = rgY1.Offset(1)
  Set rgY2 = rgY2.Offset(1)

  ss1.XValues = rgX
  ss1.Values = rgY1
  ss2.Values = rgY2
End Sub