Delete last data label on Column Chart

[gtd526]

Hello,
I have a column chart and I'm trying to ONLY delete the last Data Label (ie $65.00), which might not be the last SeriesPoint listed.
I have a chart overlay showing the Highest / Lowest value automatically. I will not delete the last data label if High/Low are the last value shown.
Thank you

 

[gtd526]

Hello,
I have a column chart and I'm trying to ONLY delete the last Data Label (ie $65.00), which might not be the last SeriesPoint listed.
I have a chart overlay showing the Highest / Lowest value automatically. I will not delete the last data label if High/Low are the last value shown.
Thank you

View attachment 120563

I'm using the following code, but it doesn't delete 2nd to Last Data Label.

Sub Delete_Last_Label()
 
  Dim mySrs As Series
  Dim iPts As Long
  Dim vYVals As Variant
  Dim vXVals As Variant

'select and activate chart
ActiveSheet.ChartObjects("WagerChart").Select

  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation
  Else
    Application.ScreenUpdating = False
    For Each mySrs In ActiveChart.SeriesCollection
      With mySrs
        vYVals = .Values
        vXVals = .XValues
        ' clear existing labels
'        .HasDataLabels = False  'gets rid of current data labels
        For iPts = .Points.Count To 1 Step -1
          If Not IsEmpty(vYVals(iPts)) And Not IsError(vYVals(iPts)) _
              And Not IsEmpty(vXVals(iPts)) And Not IsError(vXVals(iPts)) Then
            ' Delete label
                mySrs.Points(iPts).DataLabel.Delete
            Exit For
          End If
        Next
      End With
    Next
    ' legend is now unnecessary
    ActiveChart.HasLegend = False
    Application.ScreenUpdating = True
  End If

  Range("A1").Select

End Sub

 

[NdNoviceHlp]

Hi gtd526. It doesn't seem like you're having much luck with this one. I wasn't sure that you wanted a VBA solution when it was first posted. Your code looks like it should work. This syntax works...

Sheets("Sheet1").ChartObjects("ChartName").Chart.SeriesCollection(1).Points(1).DataLabel.Delete

Perhaps your conditions are incorrect re. isempty and iserror? This might be a bit tedious but you don't have a lot of series points....

If Not IsEmpty(vYVals(Ipts)) Then
MsgBox "Y val present"
End If
If Not IsError(vYVals(Ipts)) Then
MsgBox "Y val OK)"
End If
If Not IsEmpty(vXVals(Ipts)) Then
MsgBox "X val present"
End If
If Not IsError(vXVals(Ipts)) Then
MsgBox "X val OK)"
End If
If Not IsEmpty(vYVals(Ipts)) And Not IsError(vYVals(Ipts)) _
              And Not IsEmpty(vXVals(Ipts)) And Not IsError(vXVals(Ipts)) Then
MsgBox "Bingo! Point " & Ipts

HTH. Dave

 

[gtd526]

Hi gtd526. It doesn't seem like you're having much luck with this one. I wasn't sure that you wanted a VBA solution when it was first posted. Your code looks like it should work. This syntax works...

Sheets("Sheet1").ChartObjects("ChartName").Chart.SeriesCollection(1).Points(1).DataLabel.Delete

Perhaps your conditions are incorrect re. isempty and iserror? This might be a bit tedious but you don't have a lot of series points....

If Not IsEmpty(vYVals(Ipts)) Then
MsgBox "Y val present"
End If
If Not IsError(vYVals(Ipts)) Then
MsgBox "Y val OK)"
End If
If Not IsEmpty(vXVals(Ipts)) Then
MsgBox "X val present"
End If
If Not IsError(vXVals(Ipts)) Then
MsgBox "X val OK)"
End If
If Not IsEmpty(vYVals(Ipts)) And Not IsError(vYVals(Ipts)) _
              And Not IsEmpty(vXVals(Ipts)) And Not IsError(vXVals(Ipts)) Then
MsgBox "Bingo! Point " & Ipts

HTH. Dave

I've tried the code below, but receive an error. I adjusted the "ChartName" so its the same as my worksheet.

Sub Test()

ActiveSheet.ChartObjects("WagerChart").Chart.SeriesCollection(1).Points(1).DataLabel.Delete

'Sheets("Chart").ChartObjects("WagerChart").Chart.SeriesCollection(1).Points(1).DataLabel.Delete

End Sub

 

[NdNoviceHlp]

Hi gtg526. Trial using the specific sheet name as posted or perhaps the chart needs to be activated before you can access it. Also, is this an embedded chart or a separate chart sheet? The series 1 and point 1 and datalabel 1 probably also has to exist. Dave