CODEWITHSUNDEEP
excelvba
Hamtash
Hamtash

Reputation: 149

How to get rid of unwanted data series in a chart?

I am using vba to plot a chart with two data series. On the chart however it shows three legends. The third legend corresponds to dataserie3 which I did not add to the chart and I have no idea where it comes from. Also there is no data corresponds to this data series to plot.

So my first question is, do you have any idea why this legend is added, and how this data series are included in the chart? I was searching this issue and found out it is a common problem. Though all suggestions were to simply delete this data series, but I could not see the reason behind it.

My second question is, whether there is any command available in vba, such as clc, clear all, close all, ... to wipe out all data before running the code, so to avoid any unwanted data from being copied or transferred to the code?

Dim ws As Worksheet
Dim rng1X As Range, rng1Y As Range, rng2X As Range, rng2Y As Range
Dim objChrt As ChartObject
Dim chrt As Chart

Set ws = Sheets("Sheet2")
With ws
.Shapes.AddChart
Set objChrt = .ChartObjects(.ChartObjects.Count)
Set chrt = objChrt.Chart
With chrt
    .ChartType = xlXYScatterSmoothNoMarkers
    .SeriesCollection.NewSeries
    .SeriesCollection(1).XValues = rng1X
    .SeriesCollection(1).Values = rng1Y
    .SeriesCollection(1).Name = "Flow1"
    .SeriesCollection.NewSeries
    .SeriesCollection(2).XValues = rng2X
    .SeriesCollection(2).Values = rng2Y
    .SeriesCollection(2).Name = "Flow2"
    .HasLegend = True
    .Legend.Position = xlLegendPositionTop
    .Parent.Name = "Chart1"
End With

Upvotes: 4

Views: 383

Answers (2)

ArVir
ArVir

Reputation: 1

Instead of deleting undesired series, you can simply select any blank cell right before creating chart;

Sub test()

Dim Cell As Range
Dim UsedRng As Range

Set UsedRng = ActiveSheet.UsedRange

For Each Cell In UsedRng
    If Cell.Value = Empty Then
    Cell.Select
    Exit For
    End If
Next Cell

ActiveSheet.Shapes.AddChart.Select


End Sub

Upvotes: 0

Cyril
Cyril

Reputation: 6829

So, I've experienced this problem every time I make a clustered column chart using data that is non-contiguous. In testing, this did not occur for me when using contiguous data. If I step through, and inspecting the data of the chart itself, it looks like a dataseries is created when I input my XValues).

I create dataseries like:

With ActiveChart
    .ChartType = xlColumnClustered
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = " "
    .SeriesCollection(1).Values = "=Overview!$C$29,Overview!$C$32,Overview!$C$35,Overview!$C$38,Overview!$C$41"
     .SeriesCollection(1).XValues = "={"" ""}"
End With

My solution has been to remove the dataseries by looping to one dataseries above what I have defined (in this case, >1 (i = .count to 2)).

With ActiveChart
    If .SeriesCollection.Count > 1 Then
        For i = .SeriesCollection.Count To 2 Step -1
             .SeriesCollection(i).Delete
        Next
    End If
End With

Edit1:

Will spell out a better explanation as to where more than 1 dataseries has been coming from (as I only accounted for additional +1 when I said that there was a dataseries created when I specified XValues, but 2 existed previously due to my data).

When I create a chart, i create such that:

.Shapes.AddChart.Select

This immediately creates a dataseries, using data from, typically, contiguous data starting in cell A1 of the sheet, or based on your current activecell. This occurs before I even determine the type of Chart I want (in my case xlColumnClustered).

You could start removing dataseries at this point, though if you add a dataseries and specify .SeriesCollection(1) you will always be affecting the first dataseries only, whether or not it was the one you created with newseries...

You will get an error if the chart did not recognize an initial source dataseries and thus it would be preferable to add a .NewSeries and affect the "(1)" series, then go back and remove the excess later.

Specific to the chart i was creating, I have two dataseries the instant I .AddChartdue to how my data shows up in the spreadsheet. I create 1 dataseries and manipulate it how I want, then remove the excess.

Upvotes: 2

Related Questions