VBA Named Range

What is Excel VBA Named Range?

In Microsoft Excel, a VBA Named Range is a user-defined label or name assigned to a specific cell, range of cells, or constant value. It serves as a convenient way to reference data within your workbook. Named Ranges can make formulas more readable, simplify navigation, and enhance the functionality of VBA macros. When using VBA Named Ranges become even more powerful, allowing you to manipulate data and perform operations more efficiently.

In the below example, we have a financial model in Excel where we must frequently work with the data in column C, which contains the monthly revenue figures.

You can create a Named Range for this column using VBA. Here’s a simple VBA code to do that:

In this example, we use VBA to create a Named Range called “MonthlyRevenue.” The code begins by specifying the worksheet where the data is located and defines the range of cells containing the monthly revenue figures (C2:C13) as revenueRange. It then creates the Named Range “MonthlyRevenue.” It makes it easier to reference and work with the revenue data in formulas and VBA code.

What is Excel VBA Named Range?

VBA Named Range value refers to the data contained within that specific range of cells. You can access and manipulate this data programmatically using VBA code.
VBA Named Range Cell Reference allows you to refer to individual cells within a VBA Named Range using cell references. For instance, you can access a specific cell’s value, format, or properties by specifying its cell reference within the Named Range.
You can reference entire columns within the Named Range using VBA Named Range Column Reference.
It is helpful for performing operations on a column of data, such as calculating sums and averages or applying formatting.
You can use loops (such as For Each or For…Next) to iterate through the cells or elements within a VBA Named Range using VBA Named Range Loop Through. This allows you to perform actions on each cell or element sequentially.

How to Create Named Ranges?

Creating Named Ranges in Excel can be done manually without VBA. Here are the steps to create a Named Range:

Step 1: Open your Excel workbook and navigate to the worksheet where you want to create the Named Range.

Step 2: Select the cells or range of cells that you want to name by clicking and dragging.

Step 3: Locate the “Name Box” near the top-left corner of the Excel window, just above the column labels.

Step 4: Click inside the “Name Box” to activate it. Alternatively, press Ctrl + F3 to open the “Name Manager” dialog.

Step 5: Enter your desired name for the Named Range. Ensure that the name starts with a letter, contains no spaces, and does not conflict with Excel functions or keywords.

Step 6: Press the Enter key to confirm the name assignment. This action creates a Named Range referencing the selected cells with the chosen name.

Step 7: To use the Named Range in a formula or navigate to it, type its name into a cell or the formula bar. Excel will recognize and highlight it as a valid reference.

Step 8: If you need to manage Named Ranges, such as editing or deleting them, you can do so through the “Name Manager” dialog. Access it by going to the “Formulas” tab in Excel’s ribbon and clicking on “Name Manager.” From there, you can modify, delete, or define new Named Ranges as needed.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

How to Create Named Ranges using VBA Code?

Creating Named Ranges through VBA allows you to automate the process and integrate it into your Excel workflows. Here’s how you can do it with VBA code:

Step 1: Open your Excel workbook and press ALT + F11 to access the Visual Basic for Applications (VBA) editor.

Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

Step 3: Declare a VBA worksheet variable to specify the worksheet where you want to create the Named Range. For example:

Step 4: Declare a range variable to specify the range of cells that the Named Range should encompass. For example:

Step 5: Use the ThisWorkbook.Names.Add method to create the Named Range. Customize the following code according to your needs:

ThisWorkbook.Names.Add Name:=”MyNamedRange”, RefersTo:=myRange

Replace “MyNamedRange” with the desired name for your Named Range.

Ensure that RefersTo refers to the range specified in myRange.

Step 6: Close the VBA editor to return to your Excel workbook.

Step 7: To execute the VBA code and create the Named Range:

Press ALT + F8 to open the “Macro” dialog. Select the “CreateNamedRange” macro from the list.

Click the “Run” button.

Step 8: Your Named Range is now created and associated with the specified range of cells on the worksheet.

Examples

Example #1: Simple Named Range

Suppose you have a workbook with a list of expenses of items in cells A1:A10 on “Sheet1.” You can create a Named Range named “Expenses” using VBA.

Step 1: In the new module, we begin by defining a VBA subroutine called

CreateExpensesNamedRange. Subroutines are blocks of code that perform specific tasks, and in this case, our subroutine will create a Named Range.

Step 2: We declare a variable ws of type Worksheet and assign it to the worksheet named “Sheet1” within the current workbook (ThisWorkbook).

Step 3: In this step, we declare variable expensesRange of type Range. It represents a range of cells from A1 to A10 on the “Sheet1” worksheet.

Step 4: Finally, we use the ThisWorkbook.Names.Add method to create a Named Range called “Expenses.” This Named Range is associated with the range defined in expensesRange.

Step 5: Now, save the macro and click on Run.

When you run this code, it creates a Named Range named “Expenses” on “Sheet3” in Excel. This Named Range encompasses the range of cells in column A from A1 to A10, effectively defining “Expenses” as referring to these specific ten cells on “Sheet3.”

Here is the full code:

Sub CreateExpensesNamedRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(“Sheet3”)
Dim expensesRange As Range
Set expensesRange = ws.Range(“A2:A10”)
ThisWorkbook.Names.Add Name:=”Expenses”, RefersTo:=expensesRange
End Sub

Example #2: Dynamic Named Range

You can create a dynamic VBA Named Range that automatically adjusts its size based on the number of entries. Suppose you have a list of sales data in column B on “Sheet4,” and you want to create a Named Range called “SalesData” that includes all non-empty cells in column B:

Step 1: In this example, we define a VBA subroutine named CreateDynamicNamedRange. Subroutines are blocks of code designed to perform specific tasks, and in this case, our subroutine creates a dynamic Named Range.

Step 2: We declare a variable ws of type Worksheet and set it to reference the “Sheet24” worksheet within the current workbook (ThisWorkbook).

Step 3: In this step, we calculate the last row with data in column B of “Sheet2.” To do this, we use the Cells property to reference the entire column B and apply the End(xlUp) method to find the last cell with data in that column. The row number is stored in the lastRow variable.

Step 4: We declare range variable salesDataRange and set it to cover the range from cell B1 to the last row with data in column B. It creates a dynamic range that adapts automatically as data is added or removed.

Step 5: Finally, we use the ThisWorkbook.Names.Add method to create a dynamic Named Range named “SalesData.” This Named Range is associated with the dynamic range specified in the salesDataRange variable.

Step 6: Save the macro and click on run.

When you run this code, it creates a dynamic Named Range named “SalesData” on “Sheet4” in Excel. This Named Range includes all non-empty cells in column B, starting from cell B2 and extending down to the last non-empty cell in column B.

Here is the full code:

Sub CreateDynamicNamedRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(“Sheet4”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “B”).End(xlUp).Row
Dim salesDataRange As Range
Set salesDataRange = ws.Range(“B2:B” & lastRow)
ThisWorkbook.Names.Add Name:=”SalesData”, RefersTo:=salesDataRange
End Sub

Important Things To Note

VBA Named Ranges can make your Excel workbooks more organized and the formulas more understandable.
You can create VBA Named Ranges to automate repetitive tasks and streamline your workflow.
Be cautious when naming ranges to avoid conflicts with reserved keywords, cell references, or Excel functions.
You can use VBA Named Ranges in formulas, charts, data validation, and VBA code.
To edit or delete VBA Named Ranges, go to Formulas > Name Manager in Excel‘s ribbon.

Frequently Asked Questions (FAQs)

1. How do you lock a named range in VBA?

In VBA, you can lock a named range by setting the “Locked” property of the named range to “True.” It prevents any changes to the content of the cells within the named range. Locking a named range is useful when you want to protect specific data from being modified accidentally or intentionally.

2. How to remove duplicates from a named range in VBA?

To remove duplicates from a named range in VBA, you need to write code that iterates through the cells within the named range, identifies duplicate values, and then removes the duplicates. This can be achieved by using a loop, a collection, or an array to track unique values and replace the VBA named range’s contents with the unique values.

3. How do I delete all named ranges in VBA?

To delete all the VBA named ranges, you can create a loop that goes through all the named ranges in the workbook and deletes each one. This is helpful when you want to clean up and remove unnecessary named ranges from your workbook. Deleting the VBA named ranges in bulk can be done using VBA to automate the process.

Download Template

This article must be helpful to understand the VBA Named Range, with its syntax and examples. You can download the template here to use it instantly.

VBA-Named-Range Download

VBA Named Range