Deleting charts

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.
 
F

Frank Kabel

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
 
M

maxfoo

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.
 
F

Frank Kabel

Hi
not tested but try
on error resume next
Application.DisplayAlerts = False
ActiveWorkbook.Charts.Delete
on error goto 0
Application.DisplayAlerts = False
 
D

Debra Dalgleish

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
'==========================
 
M

maxfoo

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.
 
M

maxfoo

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.
 
A

Anders S

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
 
M

maxfoo

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.
 
Top