Hello Excellers and welcome to another #MacroMonday blog post in my 2019 series.  I think that today’s Excel VBA macro is a really useful one.  I hope you find it cool too.  If you have a lot of named ranges in your Excel worksheet and you need to delete them all, then a very small piece of VBA code will do the trick very quickly for you.

What Does The Macro Do?

This Macro will delete all named ranges in a workbook.  It uses the FOR…..NEXT looping method to identify named ranges and then delete each one.

How Does It Work? – Delete The Named Ranges.

FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic – Insert Module

Step 1. Declare the variable Dim NameX as name, this will simply allocate a memory container for the names ranges.

Step 2. Next,the For…Next loop begins

Step 3. Excel will loop through to find the first named ranges in the Active workbook, and delete it.

Step 4. Excel will continue through all of the named ranges until they have all been deleted.

Step 5.  The macro ends when all of the named ranges have been deleted in the active workbook.

Step 6. Test Your Macro!  This is my favourite part!.

 

 

If you want more tips then sign up to my monthly Newsletter where I share 3 Excel 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 Mondays Series or the example worksheets you can do so by clicking on the links below.

How To Excel At Excel – Macro Mondays Blog Posts.

 

Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.