Excel replace?  or VBA? - How to delete randomly placed graphic objects

Posted on 2013-01-31
Medium Priority
Last Modified: 2013-02-06
I received this worksheet and it has randomly placed tiny graphic elements.  How can I get rid of this using find/replace and/or VBA (see graphic)
Question by:brothertruffle880
  • 2
LVL 13

Assisted Solution

Shanan212 earned 400 total points
ID: 38840798
Try this

Sub Deleteall()


End sub

Open in new window

LVL 26

Accepted Solution

redmondb earned 1600 total points
ID: 38840813
Hi, brothertruffle880.

This is a problem I've dealt with here a number of times. The code below will delete all Shapes on the active sheet (there's a warning at the start).

Edit: Just seen your post, Shanan212. Yours is a vastly better solution - as long as there aren't tens of thousands of objects when an attempt to delete them all tends to crash Excel.

Sub Delete_All_Shapes()
Dim xResponse As Long
Dim xShape    As Shape

If ActiveSheet.Shapes.count = 0 Then
    MsgBox ("No Shapes in the current sheet. Run cancelled.")
    Exit Sub
End If

xResponse = MsgBox("About to delete all of the current sheet's Shapes. [" & ActiveSheet.Shapes.count & "]" & Chr(10) _
                                & "('OK' to continue, 'Cancel' to quit.)", vbOKCancel, "Delete All Shapes")
If xResponse = 2 Then Exit Sub

For Each xShape In ActiveSheet.Shapes


End Sub

Open in new window

LVL 26

Expert Comment

ID: 38860290
Thanks, brothertruffle880.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question