vba remove only last data label from column chart

gtd526

Active Member
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows
Hello,
Delete only the last data label so I can replace it when a new column is added.
Here is the code I use to add the last data label.

VBA Code:
Sub Test()
'add Last Data Label

  Dim mySrs As Series
  Dim iPts As Long
  Dim vYVals As Variant
  Dim vXVals As Variant

'select and activate chart
ActiveSheet.ChartObjects("WagersChart").Activate

  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
            ' add label
            mySrs.Points(iPts).ApplyDataLabels _
                ShowValue:=True, _
                AutoText:=True, LegendKey:=False
            Exit For
          End If
        Next
      End With
    Next
    ' legend is now unnecessary
    ActiveChart.HasLegend = False
    Application.ScreenUpdating = True
  End If

End Sub
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
387
Office Version
  1. 2016
Platform
  1. Windows
Try this ...
VBA Code:
mySrs.Points(iPts).DataLabel.Delete
 
Solution

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
387
Office Version
  1. 2016
Platform
  1. Windows
I'm glad you found this as solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top