PDA

View Full Version : Solved: help needed with vba to delete certain charts



ac31fdb
11-05-2011, 08:15 AM
ive got a workbook which contains multiple charts (not embedded) which i need to delete (in tab positions 15 + from the left). The amount of charts to delete will vary each time the macro is run. There are a number of charts (in positions 1-15) which i dont need to delete each time (these are the ones which have been listed under the "select case" txt). I have wrote the macro below which works to a point. Currently it only deletes the even numbered positioned charts. I believe its because when a chart is deleted the charts are re-numbered causing the next chart to get the number of the chart just deleted and therefore the Marco misses it. If anyone can help solving this matter it would be greatly appreciated.

Thanks

Sub Chart()
Dim ch
Dim i As Integer
Dim x As Integer
a = Sheets.Count
b = a - 1

On Error GoTo
errhandlerchart:

For i = 1 To b

Charts(i).Selectch =
ActiveChart.Name

Select Case ch
Case "Guidence notes":
Case "1 - CSO Input":
Case "Worksheets names":
Case "2 - DWF sim Data":

Case "2 - 1 Year sim data":
Case "2 - 2 Year Sim data":
Case "2 - 5 Year Sim data":
Case "2 - 10 Year Sim data":
Case "2 - 20 Year Sim data":
Case "2 - 30 Year Sim data":
Case "2 - 40 Year Sim data":
Case "3 - CSO calcs":
Case "3-CSO first spill return period":
Case "Graph Calcs {template}":
Case "CSO Graph {template}":
Case Else: ActiveChart.Delete
End Select

Next i

errhandlerchart:

End Sub

shrivallabha
11-05-2011, 09:41 AM
Your hunch could be right. To test it we can reverse the 'For Next' loop. Try the code below. Use the green VBA button to post your code:
Sub Chart()
Dim ch
Dim i As Integer
Dim x As Integer
a = Sheets.Count
b = a - 1
On Error GoTo errhandlerchart:
For i = b To 1 Step -1 'here we start at n-1 th sheet and end at sheet 1
Charts(i).Selectch = ActiveChart.Name
Select Case ch
Case "Guidence notes":
Case "1 - CSO Input":
Case "Worksheets names":
Case "2 - DWF sim Data":
Case "2 - 1 Year sim data":
Case "2 - 2 Year Sim data":
Case "2 - 5 Year Sim data":
Case "2 - 10 Year Sim data":
Case "2 - 20 Year Sim data":
Case "2 - 30 Year Sim data":
Case "2 - 40 Year Sim data":
Case "3 - CSO calcs":
Case "3-CSO first spill return period":
Case "Graph Calcs {template}":
Case "CSO Graph {template}":
Case Else: ActiveChart.Delete
End Select
Next i
errhandlerchart:
End Sub

ac31fdb
11-08-2011, 06:49 AM
Thanks Shrivallabha, your suggestion work brilliantly!