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:

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

Test the code:

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

Sample File:

NamedRange.ZIP 6.56KB 

Approved by mdmackillop


This entry has been viewed 359 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express