VBA Express : Excel - Define a Named Range

Excel

Define a Named Range

Ease of Use

Easy

Version tested with

2002

Submitted by:

Jacob Hilderbrand

Description:

The macro demonstrates how to define or change a Named Range with VBA.

Discussion:

This macro will allow you to change a Named Range with VBA. Perhaps you have a validation list that refers to a Named Range. You may want the named range to change for one reason or another. With this macro you can change the Named Range at any time.

Code:

instructions for use

			Option Explicit 
 
Sub NamedRange() 
     
    Dim Rng1            As Range 
     
     'Change the range of cells (A1:B15) to be the range of cells you want to define
    Set Rng1 = Sheets("Sheet1").Range("A1:B15") 
    ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng1 
     
End Sub

How to use:

Open Excel.
Alt + F11 to open the VBE.
Insert | Module.
Paste the code there.
Close the VBE (Alt + Q or press the X in the top right corner).

Test the code:

Tools | Macro | Macros...
Select NamedRange and press Run.

Sample File:

NamedRange.ZIP 6.56KB

Approved by mdmackillop

This entry has been viewed 364 times.