Learn About Creating VBA Named Ranges in Excel

Excel is your ultimate productivity tool. Learn how to create Named Ranges in Excel using VBA and find tips to save time when working with data and formulas.

Microsoft Excel is a powerful software application that you can use to create, track, and analyze data. In addition to the standard features of Excel, you can also create VBA macros to automate certain tasks.

Are you looking to learn about creating Named Ranges and using VBA macros in Excel? If so, you have come to the right place. In this blog post, we will discuss how to create Named Ranges in Excel using VBA.

We will also provide some examples of how you can use Named Ranges in your own work. Let's get started!

What Is a Named Range in Microsoft Excel?

Using a named range instead of a column/row reference, you can refer to a cell or group of cells with a custom name. The ability to specify the data inside your cells is a huge benefit of using Named Ranges.

Here's a quick example of how you can use Named Ranges to tell if the shipping costs are charged with the product price:

= (B7 + B5 * C4) * (1 + A3)
=(ShippingCharge + ProductPrice * Quantity) * (1 + TaxRate)

Option number two, as you can see, gives you an immediate indication of whether the price of the products includes shipping expenses. This makes it simple for the user to comprehend how the formula works without having to waste time looking through cells to figure out what is what.

How To Use Named Ranges in Excel?

As a financial analyst, you might be expected to experiment with various rates. Anything from a tax rate to a projected inflation rate might be used as an example. You can use Named Ranges to organize variables that are either modified seldom (such as Month or Year) or will remain static for a long period (like the inflation rate).

Here's a list of common names you may use often when working with Named Ranges in Excel:

ReportDate
Year
Month
FcstID
TaxRate
RawData

These are only some of the possibilities - you can name a range of anything you want that adheres to Excel's rules for Named Ranges.

Create Unique Names

Creating a Named Range is a breeze. All you have to do now is select the cell(s) you wish to refer to and type a name in the Name Box.

Because your name cannot contain any spaces, you must capitalize the first letter of each new word or use an underscore (_) to separate terms. To finalize the establishment of the Named Range, press the ENTER key after you've done typing the name.

Note: Any Named Range established with the Name Box has a Workbook scope. This means that any worksheet in your Excel file can access the named range.

Creating Names Using the Name Manager

You can change and create new Named Ranges in the Name Manager (Formulas tab > Defined Names group > Name Manager button) if you wish to customize your Named Ranges even more.

You should be aware that the Name Manager allows you to:

Change the name of a Named Range that already exists,
Make a reference formula change,
Define the scope of which worksheets the name can be accessed.

Now that you understand what a named range is and how to create one, let's look at some practical examples of how you can use them in your work.

Practical Examples of How To Use Named Ranges

Example 1: Creating a Dynamic Range

A dynamic range is a named range that automatically adjusts to include new data that is added to the worksheet. This is a great way to save time when your data changes often.

Example 2: Creating a 3D Range

A 3D range is a named range that refers to data in multiple worksheets. This can be a great time-saver if you work with large amounts of data that are organized in a similar fashion across multiple worksheets.

Example 3: Creating a Named Array

A named array is a type of dynamic range that refers to an array of values. This can be a great way to reference data that is organized in a table format.

Automate Named Ranges With VBA Macros

Now that we've gone over a quick review of Named Ranges, let's look at some VBA macros that can help you automate your use of them.

Add a Named Range

The VBA code below demonstrates how to construct various sorts of Named Ranges.

Sub NameRange_Add()

'PURPOSE: Various ways to create a Named Range

'SOURCE: SoftwareKeep.com

Dim cell As Range

Dim rng As Range

Dim RangeName As String

Dim CellName As String

'Single Cell Reference (Workbook Scope)

RangeName = "Price"

CellName = "D7"

Set cell = Worksheets("Sheet1").Range(CellName)

ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell

'Single Cell Reference (Worksheet Scope)

RangeName = "Year"

CellName = "A2"

Set cell = Worksheets("Sheet1").Range(CellName)

Worksheets("Sheet1").Names.Add Name:=RangeName, RefersTo:=cell

'Range of Cells Reference (Workbook Scope)

RangeName = "myData"

CellName = "F9:J18"

Set cell = Worksheets("Sheet1").Range(CellName)

ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell

'Secret Named Range (doesn't show up in Name Manager)

RangeName = "Username"

CellName = "L45"

Set cell = Worksheets("Sheet1").Range(CellName)

ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell, Visible:=False

End Sub

Loop Through Named Ranges

The next VBA macro code demonstrates how to cycle through your spreadsheet's Named Ranges.

Sub NamedRange_Loop()

'PURPOSE: Delete all Named Ranges in the Active Workbook

'SOURCE: SoftwareKeep.com

Dim nm As Name

'Loop through each named range in workbook

For Each nm In ActiveWorkbook.Names

Debug.Print nm.Name, nm.RefersTo

Next nm

'Loop through each named range scoped to a specific worksheet

For Each nm In Worksheets("Sheet1").Names

Debug.Print nm.Name, nm.RefersTo

Next nm

End Sub

Delete All Named Ranges

This VBA function will help you clean out a large chunk of Named Ranges in Excel that you no longer need in your project.

Sub NamedRange_DeleteAll()

'PURPOSE: Delete all Named Ranges in the ActiveWorkbook (Print Areas optional)

'SOURCE: SoftwareKeep.com

Dim nm As Name

Dim DeleteCount As Long

'Delete PrintAreas as well?

UserAnswer = MsgBox("Do you want to skip over Print Areas?", vbYesNoCancel)

If UserAnswer = vbYes Then SkipPrintAreas = True

If UserAnswer = vbCancel Then Exit Sub

'Error Handler in case Delete Function Errors out

On Error GoTo Skip

'Loop through each name and delete

For Each nm In ActiveWorkbook.Names

If SkipPrintAreas = True And Right(nm.Name, 10) = "Print_Area" Then GoTo Skip

'Error Handler in case Delete Function Errors out

On Error GoTo Skip

'Delete Named Range

nm.Delete

DeleteCount = DeleteCount + 1

Skip:

'Reset Error Handler

On Error GoTo 0

'Report Result

If DeleteCount = 1 Then

MsgBox "[1] name was removed from this workbook."

Else

MsgBox "[" & DeleteCount & "] names were removed from this workbook."

End If

End Sub

Delete Named Ranges With Errors

Only Named Ranges with faults will be deleted with this VBA code. These problems might be triggered by the deletion of worksheets or rows/columns.

Sub NamedRange_DeleteErrors()

'PURPOSE: Delete all Named Ranges with #REF error in the ActiveWorkbook

'SOURCE: SoftwareKeep.com

Dim nm As Name

Dim DeleteCount As Long

'Loop through each name and delete

For Each nm In ActiveWorkbook.Names

If InStr(1, nm.RefersTo, "#REF!") > 0 Then

'Error Handler in case Delete Function Errors out

On Error GoTo Skip

'Delete Named Range

nm.Delete

DeleteCount = DeleteCount + 1

End If

Skip:

'Reset Error Handler

On Error GoTo 0

'Report Result

If DeleteCount = 1 Then

MsgBox "[1] errorant name was removed from this workbook."

Else

MsgBox "[" & DeleteCount & "] errorant names were removed from this workbook."

End If

End Sub

TL;DR

Named ranges are a great way to organize your data in Excel.
You can use them to create dynamic ranges, 3D ranges, and named arrays.
You can also automate your use of Named Ranges with VBA macros.

Conclusion

Excel is an amazing tool that can make even the most mundane tasks exciting to complete with vast automation features. If you want to make your life easier, learning how to work with advanced tools like Named Ranges with VBA is a great way to start.

We hope this article has helped you learn a little bit more about how to use Named Ranges in Excel. As always, if you have any questions, feel free to leave us a comment below!

Thank you for reading.

One More Thing

Looking for more tips? Check out our other guides in our Blog or visit our Help Center for a wealth of information on how to troubleshoot various issues.

Sign up for our newsletter and access our blog posts, promotions, and discount codes early. Plus, you'll be the first to know about our latest guides, deals, and other exciting updates!