Create Dynamic Named Range with VBA in Excel (Step-by-Step Guideline)

Get FREE Advanced Excel Exercises with Solutions!

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.

excel vba create named range dynamic


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. However, the static named range doesn’t update by itself even if we add new data to 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.


Excel VBA to Create Dynamic Named Range: Step-by-Step Guideline

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.

Step-by-Step Guideline to Create Dynamic Named Range with VBA in Excel

  • 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

Step-by-Step Guideline to Create Dynamic Named Range with VBA in Excel

  • After that, press F5 to run the code.
  • Subsequently, close the window.

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-by-Step Guideline to Create Dynamic Named Range with VBA in Excel


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.

Step-by-Step Guideline to Create Dynamic Named Range with VBA in Excel

  • Then, under the Developer tab, select Macros.

Step-by-Step Guideline to Create Dynamic Named Range with VBA in Excel

  • 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: How to Check If Named Range Exists with Excel VBA


Download Practice Workbook

Download the following workbook to practice by yourself.


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

2 Comments
  1. Thank you very much for this code!… it works like a charm… Thanks for sharing

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo