Sabrina Ayon
In Microsoft Excel, named range can make your spreadsheets dynamic and faster to update. You can easily remove or delete unwanted Named Ranges by following the simple ways below.
Download Practice Workbook
You can download the workbook and practice with them.
4 Quick and Easy Ways to Remove Named Range in Excel
1. Using the Name Manager to Remove Named Range in Excel
Name Manager in excel is a place where you can create, edit or remove all the named ranges. This is the dataset from where we are going to remove named ranges. Here, cell range (B5:B8) is defined as Name, cell range (C5:C8) is defined as Gender and cell range (D5:D8) is defined as Age. Now let’s remove the named range ‘Age’.
STEPS:
- First, go to the Formulas tab in the ribbon. Next click on Name Manager.
- Now you can see a Name Manager dialog box. Select by clicking that you want to remove from your workbook.
- Click on the Delete.
- Then click OK.
- Finally, The selected named range is removed from your workbook.
Related Contents: How to Name a Range in Excel (5 Easy Tricks)
2. Excel Remove Multiple Named Ranges at the Same Time
You can also remove multiple named ranges at the same time.
STEPS:
- First, go to Formula > Name Manager.
- Press the Ctrl key and Click on the selected named range that you want to delete.
- Next click on the Delete
- Then OK.
Related Content: Dynamic Named in Range Excel (Both One and Two Dimensional)
3. Remove Named Range with Errors in Excel
If you have names with reference errors, go to the Filter button in the name manager to filter on Names with Errors. Then press Shift +Click to select all names and delete.
4. Delete Named Range by Using VBA Codes
You can use a simple VBA code to delete all the named ranges in excel.
STEPS:
- First, go to the Developer If you don’t find the developer tab in the ribbon you just need to Right-click on any tab from the ribbon then click on Customize the Ribbon.
- You can see Excel Options. Tick the developer box.
- Then press OK.
- Now the developer tab will appear in the ribbon. Click on the Developer Tab and then select Visual basic. This will open the visual basic editor.
- Click the Insert drop-down and select Module. This will insert a new module window.
- After that, write down the VBA code here.
VBA Code:
Sub DeleteNames()
Dim RName As Name
For Each RName In Application.ActiveWorkbook.Names
RName.Delete
Next
End Sub- Copy and paste the VBA code in the window then click on RUN or use the keyboard shortcut (F5) to execute the macro code.
- And finally, this will remove the named range from your workbook.
Related Contents: How to Use Named Range in Excel VBA (2 Ways)
Conclusion
By following these methods, you can easily remove named ranges in Excel. Hope this will help you! If you have any questions, suggestions or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
I get for the VBA one.
Run-time error ‘7’
Out of Memory
Hello, J KUMAR.
Thank you for your comment. I have tried the code too and the code is absolutely right and perfectly working. But you are facing problem because most probably your device is running out of virtual memory. So, when you are trying this code in Excel, at that time you should close all other applications. Also, you should use an individual module for this code.
Removing my named ranges, no matter the method, doesn’t fix the worksheet. It merely substitutes one error (#SPILL) with another (formulas generate incorrect answers). How do I fix Excel so it uses my dozens of named ranges?
Thanks a lot for your response John,
Here there might be some other issues underlying your file that is causing issue for you. It would be better if your share your file with us and we can have a look into it.