VBA: How to Reference a Named Range

To reference a named range in VBA, you simply need to use the Range() function with the named range in double quotes.

For example, you could create the following macro to assign a value of “Team” to every value in the named range called teams:

Sub ModifyNamedRange()
 
Range("teams").Value = "Team"

End Sub

The following example shows how to use this syntax in practice.

Example: How to Reference Named Range Using VBA

Suppose we have a named range called teams that represents the range A2:A11 in a particular sheet in Excel:

We can create the following macro to assign a text value of “Team” to each cell in this named range:

Sub ModifyNamedRange()

Range("teams").Value = "Team"

End Sub

When we run this macro, we receive the following output:

Notice that each cell in our named range now contains “Team” instead of the original team name.

We could also use the following macro to assign a numeric value of 100 to each cell in the teams named range:

Sub ModifyNamedRange()

Range("teams").Value = 100

End Sub

When we run this macro, we receive the following output:

Or we could even use the following macro to make each cell in the named range teams to have a bold font and a green background color:

Sub ModifyNamedRange()

Range("teams").Interior.Color = vbGreen
Range("teams").Font.Bold = True

End Sub

When we run this macro, we receive the following output:

Each cell in the named range teams now has a bold font and a green background color.

The following tutorials explain how to perform other common tasks in VBA: