Define a Named Range

Ease of Use


Version tested with


Submitted by:

Jacob Hilderbrand


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


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. 


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 329 times.

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