[Excel VBA] 3 Ways to Define and Delete Named Ranges

Introduction

Using Excel’s Named Range feature allows you to give a clear name like “SalesData” to a specific cell range like C2:D100. This makes formulas easier to read and makes handling specific ranges in VBA much more convenient.

By using VBA, you can automate this “Name Definition” process. In this article, I will explain two methods for defining names for cell ranges and one method for deleting them.

1. The Easiest Way: Using the Range.Name Property (Recommended)

If you just want to name a specific cell range, assigning the name directly to the .Name property of the target Range object is the simplest and most intuitive method.

Code and Explanation

Sub DefineName_Simple()

    ' Declare variables
    Dim targetRange As Range
    Dim rangeName As String
    
    ' Set the target range and the name you want to define
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("C2:E100")
    rangeName = "SalesDataTable"
    
    ' Assign the name to the .Name property of the Range object
    targetRange.Name = rangeName
    
    MsgBox "Defined the name '" & rangeName & "' for the range " & targetRange.Address(False, False)

End Sub

With just this single line targetRange.Name = rangeName, a Workbook-level name is defined for the specified range.

2. The Advanced Way: Using the Names.Add Method

Using the .Names.Add method allows for more detailed settings. For example, you can define names that are valid only within a specific worksheet (Worksheet-level names).

Code and Explanation

Sub DefineName_Advanced()

    ' Declare variables
    Dim targetRange As Range
    Dim rangeName As String
    
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("C2:E100")
    rangeName = "SalesDataTable_Advanced"

    ' Define the name using the Names.Add method
    ' Pass the target Range object to the RefersTo argument
    ThisWorkbook.Names.Add Name:=rangeName, RefersTo:=targetRange

    MsgBox "Defined the name '" & rangeName & "' using the Names.Add method."
    
End Sub

Key Points of the Code

ThisWorkbook.Names.Add Name:=Name, RefersTo:=Target

• Name: Specify the name you want to define as a string.
• RefersTo: Specify the target the name refers to. Passing the Range object directly is the most reliable method. You can also specify it as a string formula like "=Sheet1!$C$2:$E$100".

Workbook Level vs. Worksheet Level

• ThisWorkbook.Names.Add ...: Defines a name valid for the entire workbook.
• Worksheets("Sheet1").Names.Add ...: Defines a name valid only within “Sheet1”. This allows you to define the same name on different sheets without conflict.

3. How to Delete a Defined Name

To delete a defined name, retrieve the name from the Names collection and use the .Delete method.

Code and Explanation

Sub DeleteDefinedName()

    Dim nameToDelete As String
    nameToDelete = "SalesDataTable"

    On Error Resume Next ' Avoid errors if the name to be deleted does not exist
    
    ' Delete the specified name from the Names collection
    ThisWorkbook.Names(nameToDelete).Delete
    
    On Error GoTo 0
    
    MsgBox "Deleted the name '" & nameToDelete & "' (if it existed)."

End Sub

We retrieve the target name object using ThisWorkbook.Names("Name") and execute .Delete. It is common practice to use On Error Resume Next to handle cases where the name does not exist, preventing the macro from crashing.

Summary

You should choose the method for manipulating named ranges in VBA based on your purpose:

• To simply name a specific range: The simple and intuitive myRange.Name = "Name" is most recommended.
• To create Sheet-level names or advanced settings: Use the high-functionality Names.Add method.
• To delete a name: Use Names("Name").Delete.

By naming important cell ranges processed within your macros beforehand, you make your code easier to read and improve maintainability for the future.