In our earlier post, you have come to know how to delete defined names in Excel. Here we’ll discuss how to delete named ranges in Excel. Named ranges are effectively valuable while you are dealing with a huge amount of data and formulas.
With a named range, you can simply name a cell or a series of cells so that you can use those names in the formulas instead of reference. Sometimes, you may need to make named ranges and on the other side, you may need to delete some or all named ranges. It helps you reduce the bare bones while working so that you can turn files comprehensible for others.
How to Delete Named Ranges in Excel with Name Manager?
Name Manager is a feature that lets you hold all the named ranges. In case, when you need to delete some or all the named ranges from the workbook, Name Manager is helpful. Follow the steps given below to delete named ranges with the help of Name Manager:
- Click on the Formula tab and choose the Name Manager given from the Defined Names menu.
- Choose all the named ranges you need to delete from the Name Manager section.
- Press the Delete button.
- Also, note that selecting more than one named range at the same time is possible. You can do this by pressing and holding the CTRL key while clicking on it one by one.
Arguments used for Named Ranges:
With Names, you can make a formula interpretation easier. The intent of =Revenue-Costs is straightforward than =P999-P807.
- It is true when the formula refers to cells in other workbooks.
- The name formula is =Revenue-Costs
- The coordinate formula is =’Profit and loss’!P999-‘Profit and loss’!P807
- An analyst relies on Excel completely because it has rows and columns. It also reduces the need to remove the common cause of the error, referring to a cell in a different column from the one intended.
How to Delete Named Ranges in Excel By Filtering Specific Named Ranges?
Most likely, you can rely on named ranges when you have to deal with many names to be selected one by one. Name Manager once again comes to use even when you need to filter the specifically named ranges.
Suppose, you need to delete named ranges in the table only, you can do this by following these steps:
- Go to the Name Manager option placed in the Defined Named section of the Formula tab.
- Choose the Filter option from the Name Manager window. Click on the targeted Named Range from the drop-down menu. Here we are using Table Names.
- Choose the Name ranges you need to delete from the filtered list.
- Press the DELETE button.
How to Delete All Named Ranges with VBA Code?
Below you can see a short VBA code that lets you delete all the named ranges from a workbook.
- Press and hold the ALT + F11 keys and you will see the Microsoft Visual Basic for Applications window appears.
- Choose the Module option from the Insert menu and then paste the following code in the given Module Window.
VBA Code to Delete All Named Ranges in Excel:
Sub DeleteNames()’Update 20140314Dim xName As NameFor Each xName In Application.ActiveWorkbook.Names xName.DeleteNextEnd Sub
Copy
Now, press the F5 key to run this code. You will see all the names in the workbook are deleted instantly.
Summary:
Today, you have learned how to delete named ranges in Excel by using multiple tricks such as Name Manager, Filtering specific named ranges, and VBA Macro Code. Each method can be used effectively that’s why you can perform the function smoothly.
