delete SINGLE point in chart

[looping]

Hi,

I want to plot only subset of a dataset (with values between a lower and an upper threshold). To do so, I first plot all points and then delete the ones that don't match the specification:

With ActiveChart.SeriesCollection(1)
    For j = .Points.Count To 1 Step -1
        ' point coordinates
        dblX = ExecuteExcel4Macro("GET.CHART.ITEM(1,1,""S1P" & j & """)")
        dblY = ExecuteExcel4Macro("GET.CHART.ITEM(2,1,""S1P" & j & """)")
        ' delete, if x-value does not match specifications
        If dblX < dblLowerThres Or dblX > dblUpperThres Then
            ' delete point j
        End If
    Next
End With

Unfortunately, I could not figure how to delete this single point from a chart.

PS: I also posted the same problem, but with a different approach here:
http://www.mrexcel.com/forum/showthread.php?t=557947

 

[Steve=True]

Why not use offset or index/match to only plot the points you want.

Or aternately you can create a dummy series to plot and then use =NA() for points you don't want to plot?

 

[looping]

Hallo Steve,

sorry for the late reply. I have to admit, I don't really get your answer (I'm quite new to VBA).

As far as I see it, Index select a single value from a matrix/range that is specified by the row and column index the user defines.

And I also was not able to figure how to set single points to NA()

Can you please give an example with actual code? That would be of great help!

 

[SydneyGeek]

You don't need VBA.

Say your series is
12
8
25
52
35
And you only want values between 10 and 40.
In the next column you could use a formula like
=IF(AND(A2>=10,A2<=40),A2,NA())

When you plot this using a line, scatter, column or bar chart, Excel ignores the NA values and only plots the data you want to see.

Denis

 

[looping]

Hi Denis,

thanks for the answer. In a pinch, that should work.

Actually, I asked for a VBA solution because the problem described is part of a VBA process (code is below):
1) Choose the data to visualize (defined by two ranges)
2) Plot data (subdivided in intervals)
3) If wanted: plot trendlines for each interval seperately
Therefore, I would prefer a solution in VBA (I do not think it is good style to use the worksheet as a interim storage for the VBA code).

Private Sub redrawChart(strChartName As String, rngXData As Range, rngYData As Range)
    Dim i As Integer
    Dim j As Integer
    Dim dblX As Double
    Dim dblY As Double
    ' delete old data
    ActiveSheet.ChartObjects(strChartName).Activate
    For i = ActiveChart.SeriesCollection.Count To 1 Step -1
        ActiveChart.SeriesCollection(i).Delete
    Next
    ' write new data
    For i = 1 To lbxGrenzen.ListCount - 1
        With ActiveChart.SeriesCollection.NewSeries
            .XValues = rngXData
            .Values = rngYData
        End With
        ' delete points whose x values are outside the actual interval
        With ActiveChart.SeriesCollection(i)
            For j = .Points.Count To 1 Step -1
                ' point coordinates
                dblX = ExecuteExcel4Macro("GET.CHART.ITEM(1,1,""S1P" & j & """)")
                dblY = ExecuteExcel4Macro("GET.CHART.ITEM(2,1,""S1P" & j & """)")
                If dblX < lbxGrenzen.List(i - 1) Or dblX > lbxGrenzen.List(i) Then
                    ' delete points
                End If
            Next
        End With
    Next
End Sub

Here, lbxGrenzen is a listbox containing the intervals.

 

[looping]

PS: If anyone has a good idea how to edit rngXData and rngYData, that would be great, too.

 

[looping]

I found a solution: I define two new ranges with are set up by the parts of rngXData and rngYData that meet the specifications:

Private Sub redrawChart(strChartName As String, rngXData As Range, rngYData As Range)
    Dim i As Integer
    Dim j As Integer
    Dim rngXSel As Range
    Dim rngYSel As Range
    ' delete old data
    ActiveSheet.ChartObjects(strChartName).Activate
    For i = ActiveChart.SeriesCollection.Count To 1 Step -1
        ActiveChart.SeriesCollection(i).Delete
    Next
    ' write new data
    For i = 1 To lbxGrenzen.ListCount - 1
        ' rngXSel und rngYSel store the subranges that contain points in the actual interval
        Set rngXSel = Nothing
        Set rngYSel = Nothing
        For j = 1 To rngXData.rows.Count
            If rngXData(j, 1) >= CDbl(lbxGrenzen.List(i - 1)) And rngXData(j, 1) <= CDbl(lbxGrenzen.List(i)) Then
                If rngXSel Is Nothing Then
                    Set rngXSel = rngXData(j, 1)
                Else
                    Set rngXSel = Union(rngXSel, rngXData(j, 1))
                End If
                If rngYSel Is Nothing Then
                    Set rngYSel = rngYData(j, 1)
                Else
                    Set rngYSel = Union(rngYSel, rngYData(j, 1))
                End If
            End If
        Next
        With ActiveChart.SeriesCollection.NewSeries
            .XValues = rngXSel
            .Values = rngYSel
        End With
    Next
End Sub

It might not be the most elegant solution, but it works

PS: Where is the button to close a thread respectively change it's status to "answer"?

 

[doriangrey]

Please your sample file.
thanks.