How to: Refer to Named Ranges
Published: July 16, 2012
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.
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
This example uses a named range as the formula for data validation. This example requires the validation data to be on Sheet 2 in the range A2:A100. This validation data is used to validate data entered on Sheet 1 in the range D2:D10.
Sub Add_Data_Validation_From_Other_Worksheet() 'The current Excel workbook and worksheet, a range to define the data to be validated, and the target range 'to place the data in. Dim wbBook As Workbook Dim wsTarget As Worksheet Dim wsSource As Worksheet Dim rnTarget As Range Dim rnSource As Range 'Initialize the Excel objects and delete any artifacts from the last time the macro was run. Set wbBook = ThisWorkbook With wbBook Set wsSource = .Worksheets("Sheet2") Set wsTarget = .Worksheets("Sheet1") On Error Resume Next .Names("Source").Delete On Error GoTo 0 End With 'On the source worksheet, create a range in column A of up to 98 cells long, and name it "Source". With wsSource .Range(.Range("A2"), .Range("A100").End(xlUp)).Name = "Source" End With 'On the target worksheet, create a range 8 cells long in column D. Set rnTarget = wsTarget.Range("D2:D10") 'Clear out any artifacts from previous macro runs, then set up the target range with the validation data. With rnTarget .ClearContents With .Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Formula1:="=Source" 'Set up the Error dialog with the appropriate title and message .ErrorTitle = "Value Error" .ErrorMessage = "You can only choose from the list." End With End With End Sub
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
Dennis Wallentin is the author of VSTO & .NET & Excel, a blog that focuses on .NET Framework solutions for Excel and Excel Services. Dennis has been developing Excel solutions for over 20 years and is also the co-author of "Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA and .NET (2nd Edition)."