CODEWITHSUNDEEP
vbaexcel
Ariel
Ariel

Reputation: 978

VBA create chart and remove a series

I am able to create a chart for a set of data. I do not want to include the data from row 179 in the chart, however I need to keep it within the sheet.

How would I modify my chart to take row 178 (header) and row 180:182 as data series by modifying the below code?

ActiveSheet.Shapes.AddChart.Select
With ActiveChart
  .SetSourceData Source:=Range("'eod summary'!$B$178:$H$182")
  .ChartType = xlAreaStacked
  .Axes(xlCategory).CategoryType = xlCategoryScale
End With

Upvotes: 2

Views: 131

Answers (2)

user4039065
user4039065

Reputation:

Your range references are hard-coded; if this is always true then build a Union reference to be used for the SetSourceData .

ActiveSheet.Shapes.AddChart.Select
With ActiveChart
  'shorthand method
  .SetSourceData Source:=Range("'eod summary'!$B$178:$H$178,'eod summary'!$B$180:$H$182")
  'Union(..., ...) method; more verbose but also more explanatory
  '.SetSourceData Source:=Union(Range("'eod summary'!$B$178:$H$178"), _
                                Range("'eod summary'!$B$180:$H$182"))
  .ChartType = xlAreaStacked
  .Axes(xlCategory).CategoryType = xlCategoryScale
End With

Upvotes: 2

Israel Ramirez
Israel Ramirez

Reputation: 21

There is a FullSeriesCollection property you can use to define the header:

ActiveSheet.Shapes.AddChart.Select
With ActiveChart
    .SetSourceData Source:=Range("B180:H182")
    .FullSeriesCollection(1).XValues = "='eod summary'!$B$178:$H$178"
  .ChartType = xlAreaStacked
  .Axes(xlCategory).CategoryType = xlCategoryScale
End With

Upvotes: 0

Related Questions