Hi, Excellers welcome to another #MacroMonday blog. Today I want to share with you how to quickly delete any shapes that you have in your Excel workbook. Ok, I hear you say would I really have THAT many shapes in Excel worksheets. Well, I was surprised when I realised how many shapes I had generated creating a workflow diagram for a project I as was involved in. Yes, a lot. Some of them were not always visible either, hidden behind each other or stacked, formatted to be invisible, oh you name it I inherited an Excel piece of work like that. So, let’s write a quick bit of VBA code to get rid of all of the shapes from your Excel worksheet.
What Does The Macro Do?
The macro will delete ALL shapes in the active worksheet. This includes pictures, clipart, shapes, charts as well as smart art.
How Does The Macro Work?
Step 1. I want to make this macro available to any active worksheet and reuse if over it and over again so the first step in this process is to insert a new Module into my Personal Macro Workbook. If you want to read more about your Personal Macro Workbook then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Step 2. I need to declare a variable. Dim shp as Shape. This ensures Excel allocates memory for this value.
Step 3. We now use the For Next looping statement to instruct Excel to find each of the shapes in the active worksheet and it delete them. It will continue looping through the shapes until all are deleted.
Step 4. Once all shapes have been deleted the routines ends.
That is it. How simple is that? You do not need to go hunting on your worksheet to find all of the shapes. This small bit of code will do it all for you.
What Next? Want More Excel Tips?
If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series. Click on the link below
How To Excel At Excel – Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST.
