How to Remove Named Range in Excel (4 Quick Methods)

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’.

Using the Name Manager to Remove Named Range

STEPS:

  • First, go to the Formulas tab in the ribbon. Next click on Name Manager.

Using the Name Manager to Remove Named Range

  • Now you can see a Name Manager dialog box. Select by clicking that you want to remove from your workbook.

Using the Name Manager to Remove Named Range

  • Click on the Delete.
  • Then click OK.

Using the Name Manager to Remove Named Range

  • 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.

Excel Remove Multiple Named Ranges at the Same Time


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.

Remove Named Range with Errors in Excel


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.

Delete Named Range by Using VBA Codes

  • You can see Excel Options. Tick the developer box.
  • Then press OK.

Delete Named Range by Using VBA Codes

  • 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.

Delete Named Range by Using VBA Codes

  • 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

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo