Table of Contents
VBA (Visual Basic for Applications) is a programming language that can be used to automate tasks in Microsoft Excel. One task that can be accomplished using VBA is deleting a named range in an Excel worksheet. A named range is a group of cells that has been given a specific name for easier reference and manipulation. To delete a named range using VBA, the user can write a simple code that identifies the range by its name and then uses the “Range” function to delete it. This can be useful when working with large amounts of data and needing to make changes to specific ranges without manually selecting and deleting them. Overall, utilizing VBA to delete a named range in Excel can save time and streamline data management processes.
Delete Named Range Using VBA (With Example)
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: