I create about 200 rectangles on a spreadsheet, then find that to delete them
in the user interface, I've got to manually select each...can't seem to
select them all at once.
I found some answers in FAQ for a VBA solution, but can't seem to get it to
work...perhaps a limitation of Excel 97 ?

Dim WSO As Worksheet
Set WSO = ActiveSheet
WSO.Shapes.Select

Error: "Object doesn't support this property or method"

Does this mean that Excel doesn't support this, or have I missed something ?

However, the following more labour intensive method does work, but I can't
predict how many rectangles I've to select and I'm unable to work out a loop
to populate the "Rectangle 1","Rectangle 2" etc list...can't seem to be able
to create a variable that holds "Rectangle 1","Rectangle 2" without
enclosing the lot in quotes("") as it sees it as a string. Even if I could,
can't get the Array statement to accept a variable as argument list instead
of hardcoded text.

ActiveSheet.Shapes.Range(Array("Rectangle 1":"Rectangle 2")).Select

Any suggestions welcome
Thanks in anticipation.

--
Message posted via http://www.officekb.com

see Shapes
http://www.mvps.org/dmcritchie/excel/shapes.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"diglas1 via OfficeKB.com" <u22313@uwe> wrote in message news:61089a8b1ef0c@uwe...
> I create about 200 rectangles on a spreadsheet, then find that to delete them
> in the user interface, I've got to manually select each...can't seem to
> select them all at once.
> I found some answers in FAQ for a VBA solution, but can't seem to get it to
> work...perhaps a limitation of Excel 97 ?
>
> Dim WSO As Worksheet
> Set WSO = ActiveSheet
> WSO.Shapes.Select
>
> Error: "Object doesn't support this property or method"
>
> Does this mean that Excel doesn't support this, or have I missed something ?
>
> However, the following more labour intensive method does work, but I can't
> predict how many rectangles I've to select and I'm unable to work out a loop
> to populate the "Rectangle 1","Rectangle 2" etc list...can't seem to be able
> to create a variable that holds "Rectangle 1","Rectangle 2" without
> enclosing the lot in quotes("") as it sees it as a string. Even if I could,
> can't get the Array statement to accept a variable as argument list instead
> of hardcoded text.
>
> ActiveSheet.Shapes.Range(Array("Rectangle 1":"Rectangle 2")).Select
>
> Any suggestions welcome
> Thanks in anticipation.
>
> --
> Message posted via http://www.officekb.com

try
for each sh in shapes
sh.delete
next sh
or
for each sh in sheets("hasshapes").shapes
sh.delete
next sh

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"diglas1 via OfficeKB.com" <u22313@uwe> wrote in message
news:61089a8b1ef0c@uwe...
>I create about 200 rectangles on a spreadsheet, then find that to delete
>them
> in the user interface, I've got to manually select each...can't seem to
> select them all at once.
> I found some answers in FAQ for a VBA solution, but can't seem to get it
> to
> work...perhaps a limitation of Excel 97 ?
>
> Dim WSO As Worksheet
> Set WSO = ActiveSheet
> WSO.Shapes.Select
>
> Error: "Object doesn't support this property or method"
>
> Does this mean that Excel doesn't support this, or have I missed something
> ?
>
> However, the following more labour intensive method does work, but I can't
> predict how many rectangles I've to select and I'm unable to work out a
> loop
> to populate the "Rectangle 1","Rectangle 2" etc list...can't seem to be
> able
> to create a variable that holds "Rectangle 1","Rectangle 2" without
> enclosing the lot in quotes("") as it sees it as a string. Even if I
> could,
> can't get the Array statement to accept a variable as argument list
> instead
> of hardcoded text.
>
> ActiveSheet.Shapes.Range(Array("Rectangle 1":"Rectangle 2")).Select
>
> Any suggestions welcome
> Thanks in anticipation.
>
> --
> Message posted via http://www.officekb.com

David , I'm just off to bed and saw your response, so many thanks, haven't
read your site yet but looks fantastic...will dive in tomorrow.

David McRitchie wrote:
>see Shapes
> http://www.mvps.org/dmcritchie/excel/shapes.htm
>---
>HTH,
>David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
>My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
>Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
>> I create about 200 rectangles on a spreadsheet, then find that to delete them
>> in the user interface, I've got to manually select each...can't seem to
>[quoted text clipped - 22 lines]
>> Any suggestions welcome
>> Thanks in anticipation.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200605/1

Don, thanks for that, it works a treat, but what I'd prefer to do is to
select the lot so I can delete, or save the group elsewhere.



diglas1 wrote:
>David , I'm just off to bed and saw your response, so many thanks, haven't
>read your site yet but looks fantastic...will dive in tomorrow.
>
>>see Shapes
>> http://www.mvps.org/dmcritchie/excel/shapes.htm
>[quoted text clipped - 9 lines]
>>> Any suggestions welcome
>>> Thanks in anticipation.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200605/1

You can hold downt the control key>select each>copy or cut

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"diglas1 via OfficeKB.com" <u22313@uwe> wrote in message
news:6112461038471@uwe...
> Don, thanks for that, it works a treat, but what I'd prefer to do is to
> select the lot so I can delete, or save the group elsewhere.
>
>
>
> diglas1 wrote:
>>David , I'm just off to bed and saw your response, so many thanks, haven't
>>read your site yet but looks fantastic...will dive in tomorrow.
>>
>>>see Shapes
>>> http://www.mvps.org/dmcritchie/excel/shapes.htm
>>[quoted text clipped - 9 lines]
>>>> Any suggestions welcome
>>>> Thanks in anticipation.
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...l-new/200605/1

To select all the objects on the sheet --
Choose Edit>Go To, click Special
Select Objects, click OK

Then, press the Delete key, to delete the selected objects.

Or, to work with specific objects, you can add the 'Select Multiple
Objects' tool to one of your toolbars:

Choose Tools>Customize
Select the Commands tab
Select the Drawing Category
Drag the 'Select Multiple Objects' tool onto one of your toolbars
Click Close

Then, click the Select Multiple Objects button, and select any or all
objects on the worksheet.
Press the Delete key, to delete the selected objects.


diglas1 via OfficeKB.com wrote:
> Don, thanks for that, it works a treat, but what I'd prefer to do is to
> select the lot so I can delete, or save the group elsewhere.
>
>
>
> diglas1 wrote:
>
>>David , I'm just off to bed and saw your response, so many thanks, haven't
>>read your site yet but looks fantastic...will dive in tomorrow.
>>
>>
>>>see Shapes
>>> http://www.mvps.org/dmcritchie/excel/shapes.htm
>>
>>[quoted text clipped - 9 lines]
>>
>>>>Any suggestions welcome
>>>>Thanks in anticipation.
>>>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Diglas (and Debra),
Nice to know about the multiple objects.

But I think Diglas is not seeing the threading properly, and
so I'm not sure if he was actually replying to Don or to me.

But from the answers I get the impression he did not find
what I was looking at when I referred him to my shapes page
so I will provide a more specific example below and to my
htm page and to the code page.


Sub delAllRectangularShapesOnSht()
Dim shp As Shape
For Each shp In ActiveWorkbook.ActiveSheet.Shapes
'check shape code for Particular Shapes:
If shp.AutoShapeType = msoShapeRectangle Then shp.Delete
Next shp
End Sub

Right at the top of the Shapes page:
http://www.mvps.org/dmcritchie/excel/shapes.htm
is a reference to the code in
http://www.mvps.org/dmcritchie/excel/code/shapes.txt

within the shapes.txt file are two subroutines one will remove all
rectangular shapes that have their upper left corner in the
cell selection area. I've added the example above to both
the shapes.htm page and the code/shapes.txt file.

One of the reasons for having a text version of the code is to
be able to have more complete subroutines, a few additional
subroutines, and to make sure that HTML is not going to interfere
with copying code. Especially for such things as greater than,
and less than signs, and the use of ampersands.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Debra Dalgleish" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> To select all the objects on the sheet --
> Choose Edit>Go To, click Special
> Select Objects, click OK
>
> Then, press the Delete key, to delete the selected objects.
>
> Or, to work with specific objects, you can add the 'Select Multiple
> Objects' tool to one of your toolbars:
>
> Choose Tools>Customize
> Select the Commands tab
> Select the Drawing Category
> Drag the 'Select Multiple Objects' tool onto one of your toolbars
> Click Close
>
> Then, click the Select Multiple Objects button, and select any or all
> objects on the worksheet.
> Press the Delete key, to delete the selected objects.
>
>
> diglas1 via OfficeKB.com wrote:
> > Don, thanks for that, it works a treat, but what I'd prefer to do is to
> > select the lot so I can delete, or save the group elsewhere.
> >
> >
> >
> > diglas1 wrote:
> >
> >>David , I'm just off to bed and saw your response, so many thanks, haven't
> >>read your site yet but looks fantastic...will dive in tomorrow.
> >>
> >>
> >>>see Shapes
> >>> http://www.mvps.org/dmcritchie/excel/shapes.htm
> >>
> >>[quoted text clipped - 9 lines]
> >>
> >>>>Any suggestions welcome
> >>>>Thanks in anticipation.
> >>>
> >
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>

Debra,
This is great, it actually solves the problem, would never have found it
myself. Thanks a lot.

Debra Dalgleish wrote:
>To select all the objects on the sheet --
> Choose Edit>Go To, click Special
> Select Objects, click OK
>
>Then, press the Delete key, to delete the selected objects.
>
>Or, to work with specific objects, you can add the 'Select Multiple
>Objects' tool to one of your toolbars:
>
> Choose Tools>Customize
> Select the Commands tab
> Select the Drawing Category
> Drag the 'Select Multiple Objects' tool onto one of your toolbars
> Click Close
>
>Then, click the Select Multiple Objects button, and select any or all
>objects on the worksheet.
>Press the Delete key, to delete the selected objects.
>
>> Don, thanks for that, it works a treat, but what I'd prefer to do is to
>> select the lot so I can delete, or save the group elsewhere.
>[quoted text clipped - 9 lines]
>>>>>Any suggestions welcome
>>>>>Thanks in anticipation.
>

--
Message posted via http://www.officekb.com

Don,
Thanks for your suggestion, but the problem was that I had hundreds of tiny
shapes which were very difficult to select so didn't want to manually select
each...however, Debra's answer showing how to select the lot in the user
interface solves this for me.
Thanks again.



Don Guillett wrote:
>You can hold downt the control key>select each>copy or cut
>
>> Don, thanks for that, it works a treat, but what I'd prefer to do is to
>> select the lot so I can delete, or save the group elsewhere.
>[quoted text clipped - 7 lines]
>>>>> Any suggestions welcome
>>>>> Thanks in anticipation.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200605/1