VBA overflow
 

Shapes doesn't have a delete method. What you can do is

ActiveSheet.Shapes.SelectAll
Selection.Delete

--
Regards,
Tom Ogilvy


"Christof" wrote in
message ...
Chaps,

I have a macro which calls a function to delete all
shapes on a worksheet.
If that sheet holds many shapes (100+), then the code
crashes while deleting the shapes: (memory at ... could
not be read)

I've tried many implementations to delete these shapes
(deleteAll, for each, ...) but I always end up in the
same situation

Can somebody give me the best way to avoid this problem?

thanks

VBA overflow
 

You are correct that the Shapes *collection* doesn't have a Delete method. But a Shape
(singular) object does, i.e. Shapes.Delete, no, but Shapes(i).Delete should be OK.

On Tue, 2 Sep 2003 12:17:23 -0400, "Tom Ogilvy" wrote:

Shapes doesn't have a delete method. What you can do is

ActiveSheet.Shapes.SelectAll
Selection.Delete

VBA overflow
 

Actually, I think it is the selectall that causes the memory problem. Here
is some code from Dave Peterson that may be a compromise:

Search Result 2
From: Dave Peterson )
Subject: Out of memory in Excel VBA script -
ActiveSheet.Shapes.SelectAll
View: Complete Thread (4 articles)
Original Format
Newsgroups: microsoft.public.excel.programming
Date: 2002-12-24 08:18:18 PST


I added 4000 shapes (simple ovals) to a test worksheet.

This deleted them pretty slowly:

Option Explicit
Sub testme02()
Dim myShape As Shape
For Each myShape In ActiveSheet.Shapes
myShape.Delete
Next myShape
End Sub

This was a little quicker:

Option Explicit
Sub testme03()

Dim i As Long

Do While ActiveSheet.Shapes.Count 0
ActiveSheet.Shapes(1).Select
For i = 2 To Application.Min(ActiveSheet.Shapes.Count, 500)
ActiveSheet.Shapes.Range(ActiveSheet.Shapes(i).Nam e).Select
False
Next i
Selection.Delete
Loop

End Sub

(adjust 500 to the number that doesn't blow up your program.)

BTW, your original macro deleted all 4000 ovals without a problem. Neither
of
the looping methods came close to the original in speed (well, for me).

(xl2002/win98)


Bruce Berger wrote:

The subject pretty well sizes this up. I'm designing a sheet that
takes data from a web page, pastes it into Excel as HTML and then
formats it before analyzing the data. Here's the routine that's giving
problems;

Sub GetRidOfJavaScriptIcons()

' Select then delete all the javaScript and associated icons
ActiveSheet.Shapes.SelectAll
Selection.Delete
End Sub

There are about 700 Shapes associated with JavaScript code from the
HTML file, and I'd like to delete them because one cannot manually
select these cells without triggering the javascript. The size of the
sheet is destined to grow over time.

As soon as I step into the SelectAll statement I get "Out of memory
(Error 7)".

I'm open to selecting a smaller subset of Shapes and deleting them,
then stepping through the sheet and repeating, but I couldn't find a
way to select a shape other than SelectAll.

It's interesting to note that when the VB Script crashes, all of the
shapes are in deed selected, and hitting delete manually does delete
them.

Thanks for any help, and Happy Holidays.

Bruce Berger
Salem, NH, USA
--

Dave Peterson
--
Regards,
Tom Ogilvy

"Myrna Larson" wrote in message
...
You are correct that the Shapes *collection* doesn't have a Delete method.
But a Shape
(singular) object does, i.e. Shapes.Delete, no, but Shapes(i).Delete
should be OK.

On Tue, 2 Sep 2003 12:17:23 -0400, "Tom Ogilvy" wrote:

Shapes doesn't have a delete method. What you can do is

ActiveSheet.Shapes.SelectAll
Selection.Delete

VBA overflow
 

Don already offered the looping the shapes solution as the first response to
this post.

I suggested the selectall approach, but that is probably what was producing
the out of memory problem. Dave Peterson has suggested it frequently in the
past, but also said he had some problems with out of memory.

Near as I can tell, the bases have been covered - not sure what you are
trying to tell us/me here.

--
Regards,
Tom Ogilvy



"Myrna Larson" wrote in message
...
You are correct that the Shapes *collection* doesn't have a Delete method.
But a Shape
(singular) object does, i.e. Shapes.Delete, no, but Shapes(i).Delete
should be OK.

On Tue, 2 Sep 2003 12:17:23 -0400, "Tom Ogilvy" wrote:

Shapes doesn't have a delete method. What you can do is

ActiveSheet.Shapes.SelectAll
Selection.Delete