How to Delete Named Range in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

To mention the results of the formula or cell references, we use the named range. It makes our formula easy and dynamic. We can easily remember the data range reference. Also, we can use the named range to navigate between the Excel sheets swiftly. Today we are going to learn how to delete this named range in an Excel spreadsheet.


How to Delete Named Range in Excel: 3 Quick Techniques

1. Delete Named Range Manually by Using Name Manager

Assuming that we have a worksheet with lots of named ranges. We want to delete some of them manually.

Delete Named Range Manually by Using Name manager

Steps:

  • Go to the Formulas tab from the ribbon.
  • Click on the Name Manager.

Delete Named Range Manually by Using Name manager

  • The Name Manager window opens up. We can also press Ctrl+F3 to open the Name Manager window.
  • Pick the named range we want to delete by pressing the Ctrl key or the Shift key.
  • Click on the Delete.

Delete Named Range Manually by Using Name manager

  • A dialogue box pops up for confirmation. Select OK.

Delete Named Range Manually by Using Name manager

  • Now we can see that the name ranges are deleted.

Delete Named Range Manually by Using Name manager

Read More: How to Delete All Named Ranges in Excel


2. Remove Named Range with Errors in Excel

We can simply delete the named range with errors by using the Filter drop-down from the Name Manager box to filter out the names.

STEPS:

  • Go to the Formulas > Name Manager.
  • From the Name Manager window click on the Filter drop-down.
  • Select Names with Errors and we can see the result.

Remove Named Range with Errors in Excel


3. Insert VBA Codes to Delete Name Ranges

Hereby, using VBA Code we can easily delete Name Ranges.

3.1 To Delete All Named Ranges

To delete all named ranges quickly we can follow these steps.

STEPS:

  • Right-click on the mouse of the active sheet from the sheet tab.
  • Now select View Code.

Insert VBA Codes to Delete Name Ranges

  • Microsoft Visual Basic for Applications window pops up. We can get it by pressing the Alt+F11 key.
  • After that in the VBA Module copy the codes below and paste them.
Option Explicit
Sub DeleteAllRanges()
Dim Range As Name
For Each Range In ActiveWorkbook.Names
If Range.Visible Then Range.Delete
Next Range
End Sub
  • Select the Run option.

Insert VBA Codes to Delete Name Ranges

  • Now we can see that all the name ranges are deleted.

Insert VBA Codes to Delete Name Ranges


3.2 To Delete All Hidden Name Ranges

Sometimes, we can see several hidden named ranges in the worksheet. We can follow this instruction to sort out the problem.

STEPS:

  • From the sheet tab, Right-click on the sheet > View Code.

Insert VBA Codes to Delete Name Ranges

  • In Microsoft Visual Basic for Applications Module Window, copy the codes and Run the codes.
Option Explicit
Sub HiddenRanges()
Dim Ranges As Name
For Each Ranges In ActiveWorkbook.Names
If Ranges.Visible = False Then Ranges.Delete
Next Ranges
End Sub

Insert VBA Codes to Delete Name Ranges

  • At last, we will see that all the hidden named ranges are gone.

Download Practice Workbook

Download the following workbook and exercise.


Conclusion

By following these methods, we can easily delete named ranges in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Readings


<< Go Back to Named Range | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo