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.
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!
Related Articles
- How to Edit Named Range in Excel
- Edit Defined Names in Excel (Step-by-Step Guideline)
- Name a Cell in Excel (4 Easy Ways)
- How to Paste Range Names in Excel (7 Ways)