We can refer to a certain range of cells in the Excel worksheet by using the Define Name feature. But, we constantly need to add new data or update our worksheets. In that case, defining the range names, again and again, is an inconvenient process. Forming a dynamic named range can solve this problem. In this article, we’ll show you the step-by-step process to Create a Dynamic Named Range with VBA in Excel.
To illustrate, we’re going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company.
Download Practice Workbook
Download the following workbook to practice by yourself.
Dynamic Named Range in Excel
Often, we assign a name to the selected range of cells with the Define Name feature in Excel. It helps us to call a range easily with the assigned name instead of calling the cell references. But, the static named range doesn’t update by itself even if we add new data in the worksheet. We’ve to add the new row or the new column manually using the Define Name feature over again. This is quite tiresome. To avoid this issue, we can easily create a Dynamic Named Range. The Dynamic Named Range updates by itself to include the newly added row or column.
Step-by-Step Guideline to Create Dynamic Named Range with VBA in Excel
We’ll show you the complete process of creating the Dynamic Named Range with a simple VBA code in Excel. Therefore, follow the steps given below to perform the task.
STEP 1: Open the VBA Window to Create Excel Dynamic Named Range
In order to apply the code, we should open the Visual Basic window first. See the following process.
- Firstly, select Sheet1.
- Then, right-click on the mouse. There, select View Code.
- As a result, the Visual Basic window will pop out. And you’ll see a dialog box to apply the code.
STEP 2: Paste the VBA Code
- Now, copy the below code and paste it into the dialog box.
Sub NamedRangeDynamic()
Dim WS As Worksheet
Dim FirstR As Long
Dim LastR As Long
Dim FirstC As Long
Dim LastC As Long
Dim NamedRangeDynamic As Range
Dim RngName As String
Set WS = ThisWorkbook.Worksheets("Sheet1")
FirstR = 2
FirstC = 2
RngName = "DynamicNamedRange"
With WS.Cells
LastR = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastC = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set NamedRangeDynamic = .Range(.Cells(FirstR, FirstC), .Cells(LastR, LastC))
End With
ThisWorkbook.Names.Add Name:=RngName, RefersTo:=NamedRangeDynamic
End Sub
- After that, press F5 to run the code.
- Subsequently, close the window.
Read More: How to Autofill Dynamic Range Using VBA in Excel
STEP 3: Select DynamicNamedRange in the Excel Name Box
- You’ll see the desired range of cells in your worksheet with the named range DynamicNamedRange after you select it from the Name Box.
STEP 4: Check If the Created Named Range Is Dynamic
We have to check if the named range is dynamic or not. So, carry out the further operation below.
- Add new data in the 9th row like it’s shown in the following picture.
- Then, under the Developer tab, select Macros.
- Consequently, the Macro dialog box will pop out.
- There, select NamedRangeDynamic in the Macro name.
- After that, press Run.
- Subsequently, select DynamicNamedRange in the Name Box.
- As a result, it’ll return the new range including the newly added row like it’s shown below.
- Again add a new column.
- Follow the above steps to run the Macro.
- Finally, select DynamicNamedRange in the Name Box and you’ll get your expected range.
Read More: Excel Dynamic Named Range Based on Cell Value (5 Easy Ways)
Conclusion
Henceforth, you will be able to Create a Dynamic Named Range with VBA in Excel with the above-described method. Keep using it and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- Excel Dynamic Range Based On Cell Value
- Excel VBA: Dynamic Range Based on Cell Value (3 Methods)
- How to Use Dynamic Range for Last Row with VBA in Excel (3 Methods)
- Excel OFFSET Dynamic Range Multiple Columns in Effective Way
- How to Use Dynamic Range VBA in Excel (11 Ways)
- OFFSET Function to Create & Use Dynamic Range in Excel