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
 
Well rather than just copying the whole worksheet copy and Paste Special>Values.
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Not a viable option since it removes all formulas and formats. There's got to be an easy code for this, delete picture 1 thru infinity. hmmm
 
Upvote 0
May I ask please, if this is a straight-up SAP export and you want all shapes deleted, and (this is important) at the time of macro execution you do not have AutoFilter or Data Validation set on any cells (which probably would be the case with an SAP export but making sure) and you want to delete all shapes whether they are pictures or logos or buttons or what have you, then make a copy of your sheet and test this:

Dim obj
For Each obj In ActiveSheet.Shapes: obj.Delete: Next


If that does not work, try this one-liner on another test sheet:

ActiveSheet.DrawingObjects.Delete



Any luck?
 
Upvote 0
highndry said:
Not a viable option since it removes all formulas and formats. There's got to be an easy code for this, delete picture 1 thru infinity. hmmm
Well instead of paste special PasteAll.

Code:
ActiveSheet.Cells.Copy

Sheets.Add

ActiveSheet.Range("A1").PasteSpecial xlPasteAll
 
Upvote 0
"Const msoShapeTypeMixed = -2 (&HFFFFFFFE)"

So over the weekend I looked for the MsoShapeType and found it in the Object browser, and I typed mspshapedtypeMixed and it still did not delete the pics, actually gives me error.
 
Upvote 0
Did you get a chance to test either of my suggestions, neither relied on shape type, but with the caution that there are no other shapes to worry about, including if you have data validation and autofilter to consider, at the time of your macro execution. Both codelines worked for me on 2003 with various logos and shape types I placed on a sheet when testing the codes, so one of them might work for you (??).
 
Upvote 0
Tom Urtis

Thank you so much, your first code wroked like a charm. Its such a simple code, Thanks again.

I didn't see your post until this morning.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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
Back
Top