Refer to Named Range Excel VBA

Refer to Named Ranges in Excel macro  

Referring to Named Ranges in an Excel macro makes working in Microsoft Excel quite convenient and productive. Ranges are easy to identify by name in Microsoft Excel. To name a selected Excel range, click the name box at the left end of the formula bar, type a name, and then press ENTER.  The following code example refers to the range named “salary” in the Excel workbook.

Sub FormatRange()

Range(“salary”).Font.Italic = True

End Sub

To select an Excel named range, use the GoTo method in Microsoft Excel, which activates the Excel workbook and the spreadsheet and then selects the range and finally clears or deletes the contents of the range “MyRange”.

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 Excel workbook.

Sub ClearRange()

Application.Goto Reference:=”MyRange”

Selection.ClearContents

End Sub

NOTE: Macro actions cannot be undone.

Of course you can use a macro to define a named range and then access it to perform an action on it like clearing the contents as shown below:

Sub NamedRange()

Dim Range1 As Range

Set Range1 = Sheets(“Sheet1”).Range(“F4:F10″)

ActiveWorkbook.Names.Add Name:=”MyRange”, RefersTo:=Range1

Application.Goto Reference:=”MyRange”

Selection.Font.Bold=True

End Sub

Watch the Excel Training video below to see the macro in action:

Further reading:
Defined Names

VBA Express:Excel – Define a Named Range

Leave a Reply

Your email address will not be published. Required fields are marked *