Delete pic's and logos

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247
Hi

I have some pictures/logos at a spreadsheet, and I want to delete them. I recorded the following macro but when I run the macro it gives me an error it points to the third line of the code, can any one tell me what I'm doing wrong?

Sub mhiDeletePic()
'
' mhiDeletePic Macro
' Macro recorded 12/1/2005 by CONMHI
'

'
Sheets("Staffing Summary").Select
Cells.Select
ActiveSheet.Shapes("Picture 1").Select
Selection.delete
End Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
I didn't test this guy's code. But he's on the right track. Try doing a search here. I know I've seen code that does this. Basically you loop through the shapes (or oleobjects) collection for the sheet, test to make sure the object is of the type you want to delete and then just delete it.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,929
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
This works for me.
Code:
Sub DeleteAllPics() 
Dim shp As Shape 
For Each shp In ActiveSheet.Shapes 
If shp.Type = msoPicture Then shp.Delete 
Next 
End Sub
HTH
Good Luck
John
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Highndry, that is essentially the code that was on the link that I referenced. However, unlike the OP in that other thread; John did have the good sense to use a variable name that was not a keyword.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,929
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Greg/Highndry.
I appreciate the faith you have in the macro but I can't take credit for it. It is, as far as I remember, supplied by Tom Urtis.
Thanks and regards.
John
 

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247
Thank you both for your help, however it doesn't delete the logo. I'm not sure why? May be I need to put a file name instead of "msoPicture"? What do you guys think.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022

ADVERTISEMENT

Just edit the code to tell you what type of shape your logo is.

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> PrintShapesType()
    <SPAN style="color:#00007F">Dim</SPAN> shp <SPAN style="color:#00007F">As</SPAN> Shape
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> shp <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Shapes
        Debug.Print shp.Name, shp.Type
    <SPAN style="color:#00007F">Next</SPAN> shp
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

Then look up the enumeration group msoShapeType in the object browser in the VBE and add edit your IF statement to look for that too (assuming only your logo is of the type you want to delete and that your logo is, indeed, a shape residing on the WS's drawing layer).
 

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247
Hi Greg

Is it possible to find out the name (file name) of the logo. Using right click gives you properties but not the file name of the pic/gif?
 

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247
Ok slightly different approach, since that one wasn't working, from the following code:

Sheets("Staffing Summary").Select
Cells.Select
ActiveSheet.Shapes("Picture 14").Select
Selection.delete

instead of deleting picture 14, how can I delete Pinture 1 thru Picture infinity, or in other words all pictures.

Thanks
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,999
Messages
5,834,813
Members
430,323
Latest member
Regash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top