How to Delete Named Range Excel (3 Methods)

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.


Practice Workbook

Download the following workbook and exercise.


3 Quick Techniques to Delete Named Range in Excel

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


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.

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

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo