Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Using Named Ranges in a Macro.

Using Named Ranges in a Macro

by Allen Wyatt
(last updated April 26, 2016)


Bruce has a named range (Account) defined in a workbook and he wonders how to access and use that named range from within a macro. There are several ways you can access the range, using either the Range object or the Names collection.

To access the named range using the Range object, all you need to do is provide the name of the range as a parameter to the object. This name is the same one that you defined within Excel. For instance, the following line could be used to change the interior color of the entire range:

Worksheets("Sheet1").Range("Account").Interior.Color = vbYellow

Note that the Range object is used relative to a particular worksheet, in this case Sheet1. You could also define a range object within VBA and then assign it to be equal to the named range, in this manner:

Set rng = Worksheets("Sheet1").Range("Account")

The other method of using the named range is to use the Names collection. The following line will again set the interior color of the range to yellow:

Workbooks("Book1.xls").Names("Account").RefersToRange.Interior.Color = vbYellow

Note that the Names collection is relative to the entire workbook, so it is not necessary to know which worksheet the named range is associated with when you use this method of access. You can also define a range object in VBA and assign it to be the same as the named range:

Set rng = Workbooks("Book1.xls").Names("Account").RefersToRange

You should know that the Names collection method of accessing a named range will only be viable if you don't have the same named range defined on different worksheets in the workbook. If you do, then you will need to use the Range object method, which requires the use of a specific worksheet name in the reference.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12612) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Using Named Ranges in a Macro.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...


Maintaining Text Formatting in a Lookup

Want to maintain the formatting used in one cell when you use formulas to reference that text in another cell? The answer ...

Discover More

Turning Off Worksheet Tabs

Look at the bottom of a worksheet and chances are you will see tabs for all the worksheets in the current workbook. Want ...

Discover More

Excel Not Responding

Have you ever been working with data in Excel and experienced a "freeze" where the program stops responding? This can be ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Using R1C1 Formula References in a Macro

Besides the regular way of displaying formulas, Excel can also display them using what is called R1C1 format. If you are ...

Discover More

Copying Worksheet Code Automatically

When creating a workbook to be used by others, you may want any worksheets they add to the workbook to contain some ...

Discover More

Determining if Calculation is Necessary

When processing a worksheet with a macro, it may be helpful to periodically recalculate the worksheet. Wouldn't it be ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 - 4?

2016-04-27 09:21:20


you can also highlight the text, copy and paste it into your favorite word processing app and then you can see everything.

2016-04-26 09:59:03


Sorry for the frustration. The only thing that is obscured by the ad is the word "vbYellow".


2016-04-26 08:46:30

Bryan Marks

Hi, Peg,

If you hover over the ad, there is an option to click on the X and then select "Ad covers the text" to remove the ad from the page. Take it easy.

2016-04-26 08:32:31

Peg Molter

I am trying to read a very helpful hint, but the advertisement covers up vital information.

I realize you are forced to display adds to be able to do this, but when the add completely negates any usefulness of the tip, it pisses me off enough to say screw this site; I'll find my information somewhere where I can read ALL of it

2015-01-27 05:50:24

Willy Vanhaelen



If you use the Intersect method you can use range names as argument.

2015-01-27 00:56:25


Is it possible to determine the address of a named range?

I'd like to test a Workbook_SheetChange target against a list of named ranges.

2013-10-10 19:30:33

Beware using the [] constuct in conjunction with line continuation " _".

I use a lot of Excel tables and have used [TableName[ColName]] instead of Range("TableName[ColName]") extensively.

I have no problems with the former construct on a single line of code but if I use it in long commands that need a line continuation, the position of the line continuation can move unpredictably causing compile errors. For example, a line continuation entered thus:

[TableName[ColName]], _

will change to:

[TableName[ColName] _

and throw a missing end bracket error!

2013-05-08 19:40:41

Aldo Santolla

Another powerful feature with Named Ranges in macro coding is the ability to limit your access to cells within the range group.

For example say you had on your sheet cells A2:C15 named as MySales (or whatever). This named range has 14 rows and 3 columns. You can access any cell within this range using .Cell() in macro code.


TheValue = [MySales].Cells(RowNum, ColNum).Value

TheCellAddress = [MySales].Cells(RowNum, ColNum).Address

TheFormula = [MySales].Cells(RowNum, ColNum).Formula


Using [ ] within code is a syntax shortcut for using the Range() property. All functions and calls that Range() has can be accessed using [ ] as well.

TotalRows = [MySales].Rows.Count
TotalCols = [MySales].Columns.Count
ColNum = [AB23].Column


The RowNum and ColNum are variables (you can use whatever you want) and they are limited to the Named Range size ... in the above example, RowNum is 1 to 14 and ColNum is 1 to 3. Any value outside these values with raise an error.

Happy coding :-)

2013-05-06 09:54:55


Using named ranges is a very powerful piece of knowledge.

You mentioned that Names are scoped to the workbook. It is also possible to scope them to specific worksheets. I have developed a method for those reports that need to be refreshed each month/week/quarter, using the prior period's workbook as a basis.

All areas that need to be cleared are named "DataArea1", "DataArea2" etc and scoped to the worksheet. This allows me to copy a worksheet for one department for use as another and I don't have to track how many data areas I have already named.

The code for clearing the old data is

For each Worksheet in TgtWkbk
for each Name in Worksheet.Names
If Left(Name, 8) = "DataArea" then
' Sometimes it is ClearAll if formatting should be reverted
end if
Next Name
Next WorkSheet

Additional, if the number of rows or columns in the data area needs to be changed, as with a report that lists the work days of a given month or the names of the employees in the department, using Range or Row Delete/Insert within the range resets the size of the range referred to by the Name.

2013-05-06 08:11:31


Paul, this is one of the most useful tips I have ever seen on this site! Thanks for that.

2013-05-05 21:51:18

Paul Malycha

Another instance of using Square Braces.

Instead of using:
Workbooks("Book1.xls").Names("Account").RefersToRange.Interior.Color = vbYellow
This code can be used in place of the
[Account].Interior.Color = vbYellow
Much shorter to both write and read.

2013-05-05 21:44:05

Paul Malycha

I have found that the following method for useing Names in a macro is much easier to use if you have more that one sheet to refer to.
The follow is the Name "Account" used in one worksheet and the Name "Balance" in another worksheet using Square Braces to refer to the names.

cbNewBalance = [Account] & " : " & [Balance]

This method can be used effectively throughout any Module or Form in a Project.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.