Remove graph series references of deleted columns - avoid #REF! error

[TurpsUK]

Hi All,

I have a 4 sets of data in a sheet that is received through various formulas and displayed as a table. The data itself is used to plot an XY scatter. X is always fixed but the number of column value sets for Y can vary from 3 to 5, depending upon the source data, meaning the graphical data series can be 3, 4 or 5.
I've adapted a macro (below) using reference words to delete certain columns, that works well enough, courtesy of Joe4. Deleting columns based on criteria in a specified row .
However, when the graph #REF! error gets introduced, I'd like to remove those data series automatically, in the VBA code. Can anyone help with this? There are 4 graphs

And yes - the existing code references a Womble! . Idea here - something regular users would not normally write, working in excel to avoid undue column deletion.

Thank you,
Turps

Sub Delete_columns_cassette()

Dim c As Long

Sheets("Cassette").Select
Application.ScreenUpdating = False

'Loop through columns with key word

For c = 56 To 1 Step -1
If Cells(1, c).Value = "Orinoco" Then Cells(1, c).EntireColumn.Delete
Next c

Application.ScreenUpdating = True


End Sub

 

[TurpsUK]

I managed to figure this out myself, with amended code

Sub Delete_columns_cassette()

Dim c As Long

Sheets("Cassette").Select
Application.ScreenUpdating = False

'Loop through columns with key word

For c = 56 To 1 Step -1
If Cells(89, c).Value = "Orinoco" Then Cells(89, c).EntireColumn.Delete
Next c

Sheets("Cassette").Select

'Remove graph series dependent upon count of graph series in Cell A89. Blank if <3, or >4

If Cells(89, 1).Value = "" Then

End If

If Cells(89, 1).Value = "4" Then

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(5).Delete
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.FullSeriesCollection(5).Delete
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.FullSeriesCollection(5).Delete
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.FullSeriesCollection(5).Delete

ElseIf Cells(89, 1).Value = "3" Then

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(5).Delete
ActiveChart.FullSeriesCollection(4).Delete
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.FullSeriesCollection(5).Delete
ActiveChart.FullSeriesCollection(4).Delete
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.FullSeriesCollection(5).Delete
ActiveChart.FullSeriesCollection(4).Delete
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.FullSeriesCollection(5).Delete
ActiveChart.FullSeriesCollection(4).Delete

End If

Application.ScreenUpdating = True

End Sub