Delete chart from a specific cell using VBA

[Mr2017]

Hi

I've used the code below to move a chart from one sheet to another (from Sheet 5 to cell M2 in Sheet 1).

However, when re-running the code to create the chart, I would like to delete the existing chart in cell M2 of Sheet 1, without deleting the other charts in Sheet 1.

Does anyone know how to do this, please?

TIA

Sub MoveChart()
Sheet5.Activate
ActiveSheet.ChartObjects(1).Cut
Sheet1.Activate
Range("M2").Select
ActiveSheet.Paste
End Sub

 

[Domenic]

You can name your chart after you've moved it, so that you can use that same name to delete it. For example, let's say that you want to name the chart "MyChart", first delcare a constant at the module level to hold the name of the chart, and then use 2 procedures, one to move the chart and the other to delete it, as follows...

Option Explicit

Const CHART_NAME As String = "MyChart"


Sub MoveChart()
    Sheet5.ChartObjects(1).Cut
    With Sheet1
        .Paste .Range("M2")
        .ChartObjects(.ChartObjects.Count).Name = CHART_NAME
    End With
End Sub


Sub DeleteChart()
    Sheet1.ChartObjects(CHART_NAME).Delete
End Sub

Otherwise, you can use the following code to delete the chart located in M2 of Sheet1...

    Dim chrtObj As ChartObject
    
    With Sheet1
        For Each chrtObj In .ChartObjects
            If Not Intersect(chrtObj.TopLeftCell, .Range("M2")) Is Nothing Then
                chrtObj.Delete
                Exit For
            End If
        Next chrtObj
    End With

Hope this helps!

 

[Domenic]

Actually, for the procedure called DeleteChart(), we should add error handling, just in case you run it when the chart does not exist...

Sub DeleteChart()    On Error Resume Next
    Sheet1.ChartObjects(CHART_NAME).Delete
    On Error GoTo 0
End Sub

 

[Mr2017]

Cool! Thanks Domenic!