Table of Contents
Deleting named ranges in Excel using VBA is a quick and easy way to manage your data. Having the ability to delete named ranges on demand can save you time and help you keep your data organized. In this article, we will show you how to delete named ranges using VBA, with a few examples to help illustrate the process. By the end of the article, you will be able to delete named ranges with ease. Let’s get started!
You can use the following syntax in VBA to delete named ranges from an Excel workbook:
Sub DeleteNamedRanges()
Dim NamedRange As Name
For Each NamedRange In ActiveWorkbook.Names
If NamedRange.Visible Then NamedRange.Delete
Next NamedRange
End Sub
The following example shows how to use this syntax in practice.
Example: Delete Named Ranges in Excel Workbook Using VBA
Suppose we have an Excel workbook that contains the following three named ranges:
- A named range called sheet1_name in Sheet1
- A named range called sheet2_name in Sheet2
- A named range called sheet3_name in Sheet3
To see each of these named ranges, simply click the dropdown arrow in the Name Box located in the top left corner of the spreadsheet:
Suppose we would like to delete each of these named ranges.
We can create the following macro to do so:
Sub DeleteNamedRanges()
Dim NamedRange As Name
For Each NamedRange In ActiveWorkbook.Names
If NamedRange.Visible Then NamedRange.Delete
Next NamedRange
End Sub
Once we run this macro, all of the named ranges in the entire workbook will be deleted.
We can verify that they have been deleted by once again clicking on the Name Box in the top left corner of any of the sheets:
We can see that the Name Box no longer contains the names of any named ranges.