Deleting charts

  • Thread starter Thread starter maxfoo
  • Start date Start date
M

maxfoo

I'm trying to use an example in John Walkenbach excell2000 pp w/vba book on
deleting charts: 'ActiveWorkbook.Charts.Delete'

I have about 100 sheets that have data in column A and B and a chart in each
sheet. I need to delete each chart. I keep getting runtime error 1004 with the
following code.

For i = 1 To (ThisWorkbook.Sheets.Count) '# of sheets in workbook
Sheets(i).Select
Application.DisplayAlerts = False
ActiveWorkbook.Charts.Delete
Application.DisplayAlerts = False
Next i

TIA,















Remove "HeadFromButt", before replying by email.
 
Hi
try
on error resume next
For i = 1 To (ThisWorkbook.Sheets.Count) '# of sheets in workbook
Sheets(i).Select
Application.DisplayAlerts = False
ActiveWorkbook.Charts.Delete
Application.DisplayAlerts = False
Next i
on error goto 0
 
Hi
try
on error resume next
For i = 1 To (ThisWorkbook.Sheets.Count) '# of sheets in workbook
Sheets(i).Select
Application.DisplayAlerts = False
ActiveWorkbook.Charts.Delete
Application.DisplayAlerts = False
Next i
on error goto 0
Click to expand...

Nope, that just loops thru each sheet but does not delete the charts.

error 1004 is Method 'Delete' of object 'Sheet' failed.

I think 'ActiveWorkbook.Charts.Delete' is the problem. hmmmm...

Thanks anyway Frank.











Remove "HeadFromButt", before replying by email.
 
Hi
not tested but try
on error resume next
Application.DisplayAlerts = False
ActiveWorkbook.Charts.Delete
on error goto 0
Application.DisplayAlerts = False
 
The following code will delete embedded charts, and chart sheets:
'========================
Sub AllChartsDelete()
On Error GoTo ExitChart
Dim ws As Worksheet
Dim chObj As ChartObject
Application.DisplayAlerts = False
'delete embedded charts
For Each ws In ActiveWorkbook.Worksheets
For Each chObj In ws.ChartObjects
chObj.Delete
Next chObj
Next ws
'delete the chart sheets
ActiveWorkbook.Charts.Delete

ExitChart:
Application.DisplayAlerts = True
Exit Sub
End Sub
'==========================
 
Hi
not tested but try
on error resume next
Application.DisplayAlerts = False
ActiveWorkbook.Charts.Delete
on error goto 0
Application.DisplayAlerts = False
Click to expand...

Nope again...the error trap isn't the answer. error 1004 pops up again.
I need another method of deleting the charts I think...
May have to get the BIG GUNS on this one and email John Walkenbach...



Thanks again.
















Remove "HeadFromButt", before replying by email.
 
The following code will delete embedded charts, and chart sheets:
'========================
Sub AllChartsDelete()
On Error GoTo ExitChart
Dim ws As Worksheet
Dim chObj As ChartObject
Application.DisplayAlerts = False
'delete embedded charts
For Each ws In ActiveWorkbook.Worksheets
For Each chObj In ws.ChartObjects
chObj.Delete
Next chObj
Next ws
'delete the chart sheets
ActiveWorkbook.Charts.Delete

ExitChart:
Application.DisplayAlerts = True
Exit Sub
End Sub
'==========================
Click to expand...

Awesome! ChartObject was what I was missing. Thanks Deb...


















Remove "HeadFromButt", before replying by email.
 
Hi,

1. Why .select each sheet when you don't do anything on the sheet level?

2.
Application.DisplayAlerts = False
ActiveWorkbook.Charts.Delete
Application.DisplayAlerts = False
Click to expand...
This will delete chart *sheets*, not charts on a worksheet - those are shapes.

3. Try this, not thoroughly tested but you'll get the idea:
'*****
Sub test34798()
Dim wks As Worksheet
Dim sh As Shape
For Each wks In ThisWorkbook.Worksheets
With wks
For Each sh In .Shapes
If sh.Type = msoChart Then sh.Delete
Next
End With
Next
End Sub
'*****

HTH
Anders Silven
 
Hi,

1. Why .select each sheet when you don't do anything on the sheet level?

2.
This will delete chart *sheets*, not charts on a worksheet - those are shapes.

3. Try this, not thoroughly tested but you'll get the idea:
'*****
Sub test34798()
Dim wks As Worksheet
Dim sh As Shape
For Each wks In ThisWorkbook.Worksheets
With wks
For Each sh In .Shapes
If sh.Type = msoChart Then sh.Delete
Next
End With
Next
End Sub
'*****

HTH
Anders Silven
Click to expand...

Yup, I must of been three sheets to the wind when I decided to select all the
sheets in the workbook just to delete the charts...


Thanks Anders









Remove "HeadFromButt", before replying by email.
 
Back
Top