Hello:

I am trying to write/record a macro that will clear out a worksheet in
specific ranges. It is easy to deal with the text portions, but the worksheet
also contains a picture that also needs to be deleted, but the picture is
different each week and always has a different name. Is there a way to select
a shape if I can't pinpoint the name value?
Thanks.

For Each shp In ActiveSheet.Shapes
shp.Delete
Next shp

"WBTKbeezy" wrote:

> Hello:
>
> I am trying to write/record a macro that will clear out a worksheet in
> specific ranges. It is easy to deal with the text portions, but the worksheet
> also contains a picture that also needs to be deleted, but the picture is
> different each week and always has a different name. Is there a way to select
> a shape if I can't pinpoint the name value?
> Thanks.

If you want to get rid of all

Sub ShapesCut()
For Each s In ActiveSheet.Shapes
s.Cut
Next
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"WBTKbeezy" <(E-Mail Removed)> wrote in message
news:4FE68313-A8BE-4302-A2F4-(E-Mail Removed)...
> Hello:
>
> I am trying to write/record a macro that will clear out a worksheet in
> specific ranges. It is easy to deal with the text portions, but the
> worksheet
> also contains a picture that also needs to be deleted, but the picture is
> different each week and always has a different name. Is there a way to
> select
> a shape if I can't pinpoint the name value?
> Thanks.

Joel:

That works great except I failed to mention I also have two graphs on the
same sheet that I DO NOT want deleted (which your macro does), is there a way
around that caveat?

"Joel" wrote:

> For Each shp In ActiveSheet.Shapes
> shp.Delete
> Next shp
>
> "WBTKbeezy" wrote:
>
> > Hello:
> >
> > I am trying to write/record a macro that will clear out a worksheet in
> > specific ranges. It is easy to deal with the text portions, but the worksheet
> > also contains a picture that also needs to be deleted, but the picture is
> > different each week and always has a different name. Is there a way to select
> > a shape if I can't pinpoint the name value?
> > Thanks.

For Each shp In ActiveSheet.Shapes
If shp.Type <> msoChart Then
shp.Delete
End If
Next shp
"WBTKbeezy" wrote:

> Joel:
>
> That works great except I failed to mention I also have two graphs on the
> same sheet that I DO NOT want deleted (which your macro does), is there a way
> around that caveat?
>
> "Joel" wrote:
>
> > For Each shp In ActiveSheet.Shapes
> > shp.Delete
> > Next shp
> >
> > "WBTKbeezy" wrote:
> >
> > > Hello:
> > >
> > > I am trying to write/record a macro that will clear out a worksheet in
> > > specific ranges. It is easy to deal with the text portions, but the worksheet
> > > also contains a picture that also needs to be deleted, but the picture is
> > > different each week and always has a different name. Is there a way to select
> > > a shape if I can't pinpoint the name value?
> > > Thanks.

Hello
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
If sh.Type = msoPicture Then sh.Delete
Next sh

HTH
Cordially
Pascal

"WBTKbeezy" <(E-Mail Removed)> a écrit dans le message de
news: 4FE68313-A8BE-4302-A2F4-(E-Mail Removed)...
> Hello:
>
> I am trying to write/record a macro that will clear out a worksheet in
> specific ranges. It is easy to deal with the text portions, but the
> worksheet
> also contains a picture that also needs to be deleted, but the picture is
> different each week and always has a different name. Is there a way to
> select
> a shape if I can't pinpoint the name value?
> Thanks.

That does the job perfectly, thank you!

"papou" wrote:

> Hello
> Dim sh As Shape
> For Each sh In ActiveSheet.Shapes
> If sh.Type = msoPicture Then sh.Delete
> Next sh
>
> HTH
> Cordially
> Pascal
>
> "WBTKbeezy" <(E-Mail Removed)> a écrit dans le message de
> news: 4FE68313-A8BE-4302-A2F4-(E-Mail Removed)...
> > Hello:
> >
> > I am trying to write/record a macro that will clear out a worksheet in
> > specific ranges. It is easy to deal with the text portions, but the
> > worksheet
> > also contains a picture that also needs to be deleted, but the picture is
> > different each week and always has a different name. Is there a way to
> > select
> > a shape if I can't pinpoint the name value?
> > Thanks.
>
>
>

One more, simply -

ActiveSheet.Pictures.Delete ' all pictures, if any

Regards,
Peter T


"WBTKbeezy" <(E-Mail Removed)> wrote in message
news:4FE68313-A8BE-4302-A2F4-(E-Mail Removed)...
> Hello:
>
> I am trying to write/record a macro that will clear out a worksheet in
> specific ranges. It is easy to deal with the text portions, but the
worksheet
> also contains a picture that also needs to be deleted, but the picture is
> different each week and always has a different name. Is there a way to
select
> a shape if I can't pinpoint the name value?
> Thanks.

Careful, this will delete DV and autofilter arrows as well


'----------------------------------------------------------------
Sub RemoveShapes()
'----------------------------------------------------------------
' Written by : Bob Phillips
' Inspired by: Debra Dalgleish & Dave Peterson
' Improved by: Dave Peterson (cater for forms combobox)
'---------------------------------------------------------------
' Synopsis: Checks each shape to be form control, and if it
' is a dropdown, it aims to retain it.
' One problem is taht the forms combobox which is
' also a form control, and is a dropdown, so it
' does not get deleted.
'
' Catered for by testing top left of shape, as
' Autofilter and Data Validation dropdowns do not
' seem to have a topleftcell address.
'---------------------------------------------------------------
Dim shp As Shape
Dim sTopLeft As String
Dim fOK As Boolean

For Each shp In ActiveSheet.Shapes

fOK = True

sTopLeft = ""
On Error Resume Next
sTopLeft = shp.TopLeftCell.Address
On Error GoTo 0

If shp.Type = msoChart Then
fOK = False
ElseIf shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
If sTopLeft = "" Then
fOK = False 'keep it
End If
End If
End If

If fOK Then
shp.Delete
End If

Next shp

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Joel" <(E-Mail Removed)> wrote in message
news:2D878F22-E9F9-4B2E-B89C-(E-Mail Removed)...
>
> For Each shp In ActiveSheet.Shapes
> If shp.Type <> msoChart Then
> shp.Delete
> End If
> Next shp
> "WBTKbeezy" wrote:
>
>> Joel:
>>
>> That works great except I failed to mention I also have two graphs on the
>> same sheet that I DO NOT want deleted (which your macro does), is there a
>> way
>> around that caveat?
>>
>> "Joel" wrote:
>>
>> > For Each shp In ActiveSheet.Shapes
>> > shp.Delete
>> > Next shp
>> >
>> > "WBTKbeezy" wrote:
>> >
>> > > Hello:
>> > >
>> > > I am trying to write/record a macro that will clear out a worksheet
>> > > in
>> > > specific ranges. It is easy to deal with the text portions, but the
>> > > worksheet
>> > > also contains a picture that also needs to be deleted, but the
>> > > picture is
>> > > different each week and always has a different name. Is there a way
>> > > to select
>> > > a shape if I can't pinpoint the name value?
>> > > Thanks.

That is an awesome solution. Is there a way that could be done in just one
range of the worksheet? (I didn't realize I was deleting another picture I
didn't want deleted.)

"Peter T" wrote:

> One more, simply -
>
> ActiveSheet.Pictures.Delete ' all pictures, if any
>
> Regards,
> Peter T
>
>
> "WBTKbeezy" <(E-Mail Removed)> wrote in message
> news:4FE68313-A8BE-4302-A2F4-(E-Mail Removed)...
> > Hello:
> >
> > I am trying to write/record a macro that will clear out a worksheet in
> > specific ranges. It is easy to deal with the text portions, but the
> worksheet
> > also contains a picture that also needs to be deleted, but the picture is
> > different each week and always has a different name. Is there a way to
> select
> > a shape if I can't pinpoint the name value?
> > Thanks.
>
>
>