Named ranges allow you to quickly name a cell or a range of cells so that instead of using the reference, you can use those names in the formulas. And when you get hooked to using named ranged, there is a possibility that you may end up creating a lot of named ranges and may want to delete some or all of them. In this short tutorial, I will show you two simple ways to delete named ranges in Excel. You can choose to delete all the named ranges in one go, or you can choose manually or filter these and then delete them. I will also show you how to delete named cells and range using VBA. So let’s get to it!
Delete Named Ranges Using Name Manager
Excel has Name Manager – which is a place where you can manage all the named ranges (create, edit or delete). So, if you want to delete some or all of the named ranges from your workbook, you can do that using the Name Manager. Below are the steps to delete named ranges using the Name Manager: The above steps would delete the selected Named Range from the workbook. In case you want to delete multiple named ranges at one go, you can do that by selecting the ones you want to delete and then clicking the Delete button. To select multiple Named Ranges, hold the Control key and then select the Named Ranges one by one. In case you want to select a block of Named Ranges in one go, select the first one, hold the SHIFT key, and then select the last one. This will select all the Named Ranges in between and you can delete all of these in one go.
Filtering Named Ranges
As you become more proficient with Excel and start using Named Ranges regularly, there is a possibility that you will have a lot of named cells and ranges in a workbook. While the Name Manager does make it easy to handle these named ranges, it could still become quite cumbersome when you have a lot of these. Name Manager allows you to quickly filter Named Ranges based on the following criteria:
Names scoped to the worksheetNames scoped to the workbookNames with errorsNamed without errorsDefined NamesTable Names
You can use these filters from the Name Manager itself (using the Filter option at the top-right of the Name Manager dialog box)
Delete Named Ranges using VBA
While using the Name Manager is an efficient way to delete named ranges, if you need to do this quite often, you can use a simple VBA code to delete all the named ranges or named ranges that contain a specific text string. Below is the VBA code that will delete all the Named Ranges from the entire workbook. The above code uses the For Next loop to go through all the Named Ranges one by one and delete them. After running this VBA code, your workbook will not have any Named Ranges in it. You can also tweak this code a little to delete only those Named Ranges that contain a specific word. For example, let’s say I want to only delete those Names where it contains the word ‘sales’ (anywhere in the name). You can do this by using the below VBA code: The above code again loops through each name in the Workbook and then checks whether the name contains the word sales or not. This is done using the VBA INSTR function along with an IF Then condition. Only those names where it contains the word ‘sales’ would be deleted and all the rest would be ignored. Now, the big question – how do you use this code? Below are the steps to use this code in your Excel file: The above steps would run the VBA code and delete the named ranges from your workbook. Note that the change made to the VBA code is not reversible. So as a good practice, make sure you create a backup copy of your workbook before running the VBA code. So these are two ways (manual and VBA) you can use to delete named ranges in Excel. While the Name Manager allows you to go through all the named ranges and in the workbook and then you can choose and delete some (or all), if you want to delete all named ranges in one go, you can use the VBA method. I hope you found this tutorial useful! Other Excel tutorials you may also like:
Useful Excel Macro Code ExamplesCreating a Drop Down List in ExcelHow to Reference Another Sheet or Workbook in ExcelHow to Rename a Sheet in Excel (4 Easy Ways + Shortcut)
