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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Paste Values Only with No Formatting
VBA: How to Copy Visible Rows to Another Sheet
VBA: Copy Rows to Another Sheet Based on Criteria
