Adding and Resizing Named Ranges in Excel using VBA

Learn how to add and resize Named Ranges so that you can write VBA code to enhance your spreadsheets using Named Ranges.

In this 10 minute video we cover:

• The difference between adding a Named Range scoped to a Workbook vs Worksheet
• How to use the .Add method with parameters Name:=RangeName and RefersTo:=Rng
• Adding a hidden Named Range that’s invisible to the Excel Name Manager dialog box… so that you can create private Named Ranges in VBA that a casual Excel user won’t be able to modify
• How to quickly resize a Named Range without first deleting it… using the .Resize method and .Name property

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]