Selecting and Looping Named Ranges in Excel using VBA

We take a look at how to select Named Ranges and use loops to perform actions on many Named Ranges.

In this 10 minute video we cover:

• Selecting Named Ranges on the active worksheet using the .Select method
• Why you can’t use the .Select method to select Named Ranges on a worksheet that’s not active… and how to select Named Ranges on another worksheet using the Application.Goto function
• Using square brackets […] to refer to ranges – this is the same as using the Application.Evaluate function but is a neat way to refer to ranges because it’s very easy to type
• Looping through all Named Ranges in a workbook
• Hiding all Named Ranges in a workbook so they are not visible to the casual user in the Excel Name Manager dialog box (and how to make them visible again)
• Printing all Named Ranges to the VBA Editor Immediate Window

Note on Names that can be used

Named Ranges in Excel can contain letters, numbers, and some special characters.

Here are some rules for Named Ranges:

• The first character must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
• Names cannot be the same as a cell reference, such as Z$100 or R1C1.
• Spaces are not allowed. Underscore characters and periods may be used as word separators for example “First.Quarter” or “Sales_Tax”
• A name can contain up to 255 characters.
• Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.

Write your comments and feedback below

• If you have questions or comments about this lesson, write it in the section below.
• Alternatively you can email the instructor Victor at [email protected]