Shapes(1).DeleteInstead, you must specify the sheet, using code similar to any of the following lines:
ActiveSheet.Shapes(1).Delete Sheets("Sheet1").Shapes(1).Delete Sheets(1).Shapes("Signature").DeleteIf you determine that the expected image is not in the Shapes collection, then it is possible that Excel (for strange reasons only known to Excel) moved the image to a different collection, such as the Pictures collection. If you suspect this, then try using the following macro:
Sub WhatAmI() Dim sTemp As String sTemp = "You selected this type of object: " & TypeName(Selection) sTemp = sTemp & vbCrLf sTemp = sTemp & "It's name is " & Selection.Name MsgBox sTemp End SubSelect the signature image, then run the macro. You should see a message box that indicates the type of object you selected, along with its name. You can then use the information to modify your macro so it deletes the image, as desired.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10037) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Removing Pictures for a Worksheet in VBA.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When working with text in Excel, you can slice and dice it in many ways. This tip shows how to pull first letters from ...
Discover MoreExcel allows you to hide worksheets so that they aren't visible to those using your workbook. Hiding worksheets has a ...
Discover MoreWhen you have a macro that processes a huge amount of data, it can seem like it takes forever to finish up. These ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2015-07-29 14:11:42
satindar
Thanks a lot!! I had been searching way to delete images that were thousands in number in worksheet. Saved lot of time. thanks again for the tip.
2014-08-21 11:03:56
greg
Sub shapedelete()
Dim shp As Shape
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
For Each shp In ws.Shapes
MsgBox (shp.Type) 'can be commented out later
If (shp.Type = 13 Or shp.Type = 8 Or shp.Type = 1) Then shp.Delete
Next shp
Next ws
End Sub
2014-07-07 06:56:00
Martin Shingler
Excellent - used it within hours. Thanks
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Comments