Erase All shapes, lines, Boxes

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
I know someone has written about erasing buttons and not objects.
I want to do just the opposite. I would like to erase all the arrows, boxes, circles, etc. at once through code. How could I do this??

Thank You,
Michael
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jdc

Active Member
Joined
Nov 23, 2004
Messages
263
Hi

Would this be what you are looking for

Code ***************

Sub DeleteShapes()
For Each c In ActiveSheet.Shapes
c.Select
Selection.Delete
Next c
End Sub

********************

I am not sure just how much it will delete, it will remove Command buttons,

John
 

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Try this

Sub EraseDrawings()
Dim mySht As Worksheet

Set mySht = Worksheets("Sheet1")
mySht.Shapes.SelectAll
Selection.Delete

End Sub
Sub AddaDate()


It will delete ALL shapes including Control Toolbox items.

Regards
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
You were right it deleted everything.
I created a button with my macro in it, and sure enough away it went with everything else. :LOL:

I wonder if there is a way?
Michael
 

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487

ADVERTISEMENT

Michael

Draw a Forms button on your sheet and assign your macro. Then try this code - amend the button name to that of your button - look in the names box just above column A for the name when you right click the button.

Sub EraseDrawings()
Dim mySht As Worksheet
Dim sh As Shape

Set mySht = Worksheets("Sheet1")
For Each sh In mySht.Shapes
If sh.Name <> "Button 1" Then
sh.Delete
End If
Next sh
End Sub


Regards
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Yes that worked GREAT!! Thank You sooooooo much.
I had it all set up and it wasn't working?? I thought why is this not working? I went to the code and realized it was for "Sheet 1". As soon as I changed to my sheets name, It worked perfectly.

Thank You,
Michael
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Instead of Deleting, Can I count all the shapes except the button to execute the Macro?

What would I add or write??

Thank You,
Michael
 

Glaswegian

Well-known Member
Joined
Oct 14, 2003
Messages
1,487
Hi Michael

Would this do? Just a simple count less the one for your button.

Sub CountDrawings()
Dim mySht As Worksheet

Set mySht = Worksheets("Sheet1")
MsgBox "Found " & (mySht.Shapes.Count - 1) & " shapes!"
End Sub


Regards
 

Forum statistics

Threads
1,148,269
Messages
5,745,781
Members
423,972
Latest member
franklins

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