How to: Refer to Named Ranges

  • Article
Excel Developer Reference

Ranges are easier to identify by name than by A1 notation. To name a selected range, click the name box at the left end of the formula bar, type a name, and then press ENTER.

Referring to a Named Range

The following example refers to the range named "MyRange" in the workbook named "MyBook.xls."

  Sub FormatRange()
    Range("MyBook.xls!MyRange").Font.Italic = True
End Sub

The following example refers to the worksheet-specific range named "Sheet1!Sales" in the workbook named "Report.xls."

  Sub FormatSales()
    Range("[Report.xls]Sheet1!Sales").BorderAround Weight:=xlthin
End Sub

To select a named range, use the GoTo method, which activates the workbook and the worksheet and then selects the range.

  Sub ClearRange()
    Application.Goto Reference:="MyBook.xls!MyRange"
    Selection.ClearContents
End Sub

The following example shows how the same procedure would be written for the active workbook.

  Sub ClearRange()
    Application.Goto Reference:="MyRange"
    Selection.ClearContents
End Sub

Looping Through Cells in a Named Range

The following example loops through each cell in a named range by using a For Each...Next loop. If the value of any cell in the range exceeds the value of limit, the cell color is changed to yellow.

  Sub ApplyColor()
    Const Limit As Integer = 25
    For Each c In Range("MyRange")
        If c.Value > Limit Then
            c.Interior.ColorIndex = 27
        End If
    Next c
End Sub

See Also