Lack of testing and a poor memory on my part (as well as bad typing):

Sorry on all 3 parts.

Try this one instead:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim shp As Shape

For Each wks In Worksheets
wks.Select
wks.Shapes.SelectAll
On Error Resume Next
Selection.Delete
If Err.Number < 0 Then
Err.Clear
For Each shp In wks.Shapes
shp.Delete
Next shp
End If
On Error GoTo 0
Next wks
End Sub

And sorry about (all!) the errors in the previous suggestion.


wrote:

DAve,

When I try to execute the macro below (I left off Option Explicit()):

Sub testme()
Dim wks As Worksheet
For Each wks In Worksheets
wks.Shapes.Delete
Next wks
End Sub

When the systems tries to execute the "wks.Shapes.Delete" line, I get an
"Object doesn't support this property or method.". I also tried another
variation below that didn't work:

Sub testme()
Dim wks As Worksheet
wks = Sheets("Home")
ActiveSheet.Shapes.Delete
End Sub

and

Sub testme()
Dim wks As Worksheet
wks = Sheets("Home")
wks.Shapes.Delete
End Sub

I'm just guessing. I have about 2000 lines of VBA code that is spread over
11 macros, but my coding is very primitive and brute force. Please let me
know where I'm missing it.

Thanks,
John

"Dave Peterson" wrote:

You want to delete them all?

You could use a macro:

Option Explicit()
sub testme()
dim wks as worksheet
for each wks in worksheets
wks.shapes.delete
next wks
end sub

Hopefully that won't break when there are so many shapes in each sheet.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

DAve, amazing. 8 out of 9 sheets show the exact same number of objects or
shapes.....22,913!! I have no idea how they got there. Can you now tell me
how to view them and then delete them? Thanks, John

"Dave Peterson" wrote:

Yep, those shapes would be objects.

Sometimes people add pictures to the worksheets and those pictures can be pretty
large.

Open your troublesome workbook.
Hit alt-F11 to get to the VBE
hit ctrl-g to see the immediate window
Type this and hit enter:

?activesheet.shapes.count

Swap back to excel and change sheets and do it again. Maybe there are shapes
you can't see???

John Childs wrote:

Dave, thanks for hanging with me. Pardon my ignorance, but is a box formed
using the Autoshape tool called an "object"? If so, yes I do have have about
10 objects on my sheet.

Still two mysteries to me: 1) sometimes I can run a VBA macro and the file
size stays absolutely the same. Then I run it again, and the size increases.
I know I must be doing something different each time, but I haven't narrowed
it down yet.

2) Any clue on why a completed stripped/deleted sheet including macros could
still show a file size of 2.56MB?

Thanks,
John

"Dave Peterson" wrote:

Do you have any objects on any of the worksheets?

Edit|Goto|special|objects
may help you find them.

wrote:

Can't determine why Excel 2003 file size is growing.

Stripped my spreadsheet. Erased all VBA macros. Dumped all temp files.
Last Cell & UsedRange don't appear to be issues. Not tracking changes. No
pivot tables.

And still I wind up with 2.56MB sitting somewhere. I originally posted
recently under subj of "Excel File Shrink". I started with a 3 MB file that
with very little additonal data entry grew to 60MB.

Hopefully somebody out there has an answer before I have to pony up and pay
MS $245 for Pro Level tech support.

Thanks,
John


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson