Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is format overflow? | New Users to Excel | |||
Overflow error.. why? | Excel Discussion (Misc queries) | |||
Data overflow to second worksheet | Excel Worksheet Functions | |||
Overflow Error | Excel Discussion (Misc queries) | |||
VBA overflow | Excel Programming |