Dynamic Named Range using VBA in Excel

 In previous article Create Dynamic Named Range in Excel we learn't multiple methods to create Named Range in Excel and use them dynamically to get our work done.

In this article we will try to learn and understand:

How to create Dynamic Named Range using VBA Coding in Excel?
How to use Named Range dynamically in Data Validation?

Create Dynamic Named Range using VBA in Excel

Creating Named Range and using dynamically in formulas or referencing is useful method in excel.
At times we want to create Named Ranges in excel for every now and then and for every naming we need to write the formula. 

How to create Dynamic Named Range using VBA Coding in Excel?

1. Decide the range on which you want to create Name Range programmatically.
2. Press F11 to go to VBA Editor in Excel, see below image window of VBA Editor.
3. Even you can click on Developer Tab -> Visual Basic button on Ribbon to go on VBA Editor.

 Start writing the vba code by assigning the meaningful name to procedure, as I have given 
    Sub CreateNamedRangeinVBA

Second step is to assign variable to store values
    Dim LastRow as Long 'Data Type as long
       This will determine the last used row always when ever you execute the code and store 
       the row number in LastRow variable.
   Dim NameRange, ValidationRng as Range 'Data Type as Range
       These variable will set the defined range and store in it.

Now set value of Last row 
        LastRow = Sheets(2).Cells(Rows.Count, 2).End(xlUP).Row
        We are saying as to determine LastRow value in Sheet2 using cells property by counting 
        number of used rows in column number 2

Set NameRng range to use in Named Range for Creating dynamic Named Range. In this case
       Set NameRng = Range("B5", "B" & LastRow)
       We are saying that to use Range B5 to B and LastRow value as Named Range.

Set ValidationRng as to where we are applying Data Validation, in this case
       Set ValidationRng = Range("E6:E8")
       We are saying that apply data validation drop down between range E6 to E8

Give meaningful name to Named Range, in this case I had given as "Months" and the same will be used in Data Validation and by referencing the range as NameRng.

How to use Named Range dynamically in Data Validation?

Final task is to assign the Named Range to Data Validation programmatically and use in desired Range in this case range is Range("E6:E8") which we had already set as ValidationRng.
Write code by applying all required properties for Validation as shown in below image. 

Once done with all above code writing, set all variable to nothing as best practice.

With this method we can apply Dynamic Named Range and use them programmatically.








Related Article: Create Dynamic Named Range in Excel
Downloads:       Excel VBA to create Named Range and use in Data Validation