Learn About Creating VBA Named Ranges in Excel

When it comes to productivity, Excel is king. Discover time-saving techniques for dealing with data and calculations, including how to use VBA to construct Named Ranges in Excel.

When it comes to creating, tracking, and analyzing data, Microsoft Excel is a formidable tool. To go beyond Excel’s standard features, you can automate certain tasks using VBA macros.

Do you want to know how to use Excel VBA macros and create Named Ranges? There is no better spot to be in that situation. Named Ranges can be created in Excel with the help of the Visual Basic for Applications (VBA) macro.

Also, to help you understand how named ranges function, we will give you multiple examples of their use. Great, then we can get going!

A Named Range in Excel: What Is It?

Instead of using column or row references, a named range lets you refer to a cell or set of cells by a custom name. You may specify the data inside your cells using Named Ranges, which is a big advantage.

To see if the shipping fees are included in the purchase price, here’s a brief example of using Named Ranges:

1. = (1 + A3) * (B7 + B5 * C4)
2. Product price multiplied by quantity, plus shipping charge (one plus the tax rate)

You can see that the second option immediately tells you if the product price includes shipping costs. Because of this, the user may quickly understand the formula’s operation without wasting time searching through cells to find their information.

How To Use Named Ranges in Excel?

You may be required to try out different rates in your role as a financial analyst. One possible example is a tax rate, but it might also be a predicted inflation rate. Named Ranges are a great way to label variables that do not change often (like Month or Year) or that will not change for a long time (like the inflation rate).

A few names that come up frequently when dealing with Named Ranges in Excel are:

• Document Date
• Year
• Month
• FcstID data
• Tax Ratio
• Data Set

These are just a few examples; according to Excel’s Named Ranges principles, you can name a range of anything you like.

Make Distinct Nicknames

It is very easy to create a Named Range. Pick the cell or cells you want to use as a reference and then enter their name in the Name Box.

Please ensure that the first letter of each new word is capitalized or that an underscore (_) is used to separate terms since your name cannot contain spaces. Once the name has been entered, press the ENTER key to finish establishing the Named Range.

Keep in mind that there is a Workbook scope for any Named Range that is created using the Name Box. If you name a range in Excel, any worksheet in your file can access it.

The Name Manager: A Tool for Creating Unique Names

Under the Formulas tab, under the Defined Names group, you’ll find the Name Manager button. From there, you may change or create new Named Ranges to better customize your data.

Also, remember that the Name Manager grants you the authority to:

• Modify an existing Named Range’s name,
• Modify the reference formula,
• Establish the range of worksheets that can be accessed by the given name.

Now that you know what a named range is and how to make one, we can examine some real-world examples of their use.

Use of Named Ranges in Real-World Applications

Building a Dynamic Range: An Example

A named range that adapts mechanically to incorporate newly entered data into the worksheet is known as a dynamic range. To save time when data is frequently updated, this is a fantastic solution.

Section 2: Making a Three-Dimensional Range

Data from many worksheets can be referenced using a named range called a 3D range. If your data is organized similarly across numerous worksheets and you deal with large amounts of information, this can save you a lot of time.

Third Example: Initializing a Named Array

An array of values referred to as a named array is a specific kind of dynamic range. Data presented in a tabular format can be cited in this way.

Create Named Ranges Automation Using VBA Macros

Following this brief introduction, we will examine many VBA macros that can automate the use of Named Ranges.

Set Up a Named Range

Here is a sample VBA code that shows how to create several types of Named Ranges.

Sub NameRange_Add()

‘PURPOSE: Various ways to create a Named Range

‘SOURCE: Fastsoftwares.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

 

Cycle Through Identified Sets

The following code snippet shows you how to iterate over the Named Ranges in your Excel spreadsheet using VBA.

Sub NamedRange_Loop()

‘PURPOSE: Delete all Named Ranges in the Active Workbook

‘SOURCE: Fastsoftwares.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

 

Remove Every Named Range

If your project no longer requires a significant number of Named Ranges in Excel, you can remove them with the help of this VBA function.

Sub NamedRange_DeleteAll()

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

‘SOURCE: Fastsoftwares.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

Next

‘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

 

Remove Named Ranges When Errors Occur

This code in Visual Basic for Applications will only remove Named Ranges that have errors. The removal of worksheets or rows/columns could cause these issues.

Sub NamedRange_DeleteErrors()

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

‘SOURCE: Fastsoftwares.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

Next

‘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

 

Simply put,

• An excellent method for organizing data in Excel is named ranges.
• Named arrays, dynamic ranges, and 3D ranges are all possible with their help.
• Named Ranges can also be automated using VBA macros.

In summary

Excel is a fantastic program with many automation capabilities that may make any job more fun to do. Mastering VBA’s more advanced features, such as Named Ranges, is an excellent first step toward a simpler existence.

We hope you have gained some useful knowledge about Named Ranges in Excel from this article. Post a comment below if you need any further clarification or have any questions.

All eyes are on you now.

Something Else

Would you like more guidance? For a plethora of information on how to handle various situations, check out our Help Centre or peruse our other guides in the Blog.

By signing up for our newsletter, you’ll be the first to know about upcoming blog entries, sales, and discount codes. When we release new guides, have sales, or make any other significant improvements, we will be sure to let you know first!