How to Use Named Range for Data Validation List with VBA in Excel

If you are looking for the easiest ways to use named range for data validation list in Excel VBA, then you will find this article useful. Named ranges are useful to use in a data validation formula to make a dropdown list easily and this task can be made super easy with the help of some VBA codes.
So, let’s start our main article to explore the ways of using named ranges in a data validation list.

Download Workbook

4 Ways to Use Named Range for Data Validation List in Excel with VBA

Here, we have the following dataset containing the records of some products and their respective salesperson’s lists. Using this dataset we will try to show different ways with different VBA codes to use named ranges in a data validation list.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Method-1: Using Named Range in Data Validation for Making a Dropdown List

Here, we have named the range of the Fruits column with Fruits and using a VBA code we will create a dropdown list in cell D6.

Step-01:
➤ Go to the Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

After that, a Module will be created.

Step-02:
➤ Write the following code

Sub Datavalidation1()

Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"

End Sub

Here, Validation will be added to cell D6, xlValidateList is for creating a dropdown list and the formula is used as the name of the range “=Fruits”.

➤ Press F5 and later click on the dropdown symbol of cell D6.
Then, you will get the list of the fruits and select any one item from the list like the Cherries.

Finally, we are getting our selected item in cell D6.

Read More: How to Make a Data Validation List from Table in Excel (3 Methods)

Method-2: Adding Named Range and a Data Validation List with a VBA Code

We will not create any named range here manually, rather a simple VBA code will form a named range, and then, using it we will get the dropdown list finally in cell D6.

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

Sub Datavalidation2()

ActiveWorkbook.Names.Add Name:="Fruit", _
RefersTo:=ThisWorkbook.Worksheets("Add").Range("B4:B10")
Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruit"

End Sub

Firstly, it will add the name Fruit to the range “B4:B10” of the worksheet Add.
Then, we will add Validation to the cell D6, xlValidateList is for creating a dropdown list and the formula is used as the name of the range “=Fruit”.

➤ Press F5, then, go to the worksheet and click on the dropdown symbol of cell D6.
Later, you will get the list of the fruits and select any one item from the list like the Blueberries.

So, we have got our desired item Blueberries from the list and besides this, we can see our created named range for the fruits.

Related Content: Create Data Validation Drop-Down List with Multiple Selection in Excel

Similar Readings:

• Autocomplete Data Validation Drop Down List in Excel (2 Methods)
• Excel Data Validation Drop Down List with Filter (2 Examples)
• Default Value in Data Validation List with Excel VBA (Macro and UserForm)
• Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)
• Excel Data Validation Alphanumeric Only (Using Custom Formula)

Method-3: Updating Data Validation List with Named Range Using Excel VBA

Suppose, we have the following dropdown list in cell D6, which works fine for the fixed dataset.

But, if we add an extra vegetable Lettuce then it will not appear in the dropdown list which means our dropdown list is not updated automatically in this case.

To update the list quickly and automatically you can follow this method.

3.1: Creating the Updated Named Range

Firstly, we have to add a name for the range of Column B in a way that it will automatically take the newly added items into this name.
➤ Go to the Formulas Tab >> Defined Names Group >> Name Manager Option.

Then, the Name Manager dialog box will open up.
➤ Click on the New option.

After that, the Edit Name wizard will pop up.
➤ Write down Vegetables in the Name box and the following formula in the Refers to box and finally press OK.

Here, Update! Is the sheet name, $B$4 is the reference cell from which we want to move, 0 for Rows and Columns arguments means it will remain in its reference or starting position.
COUNTA will count the number of cells with any type of values in Column B and then 2 will be subtracted because of the heading of the dataset in B1 and the header of the column in B3. So, you will get only the number of cells containing any vegetables.
This number will be the return reference from the starting position and OFFSET will always return the updated named range here.

Then, you will be taken to the Name Manager wizard.
➤ Press Close.

3.2: Using a VBA Code to Apply Data Validation List

➤ Right-click on the sheet name and select the View Code option.

Afterward, the code window will appear.

➤ Type the following code

Sub worksheet_Change(ByVal newitem As Range)

Dim updatedrange, item
If Not Intersect(newitem, Range("B:B")) Is Nothing Then
For Each item In Range("Vegetables")
updatedrange = updatedrange & "," & item
Next item
With ActiveSheet.Range("D6").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:=updatedrange
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
End With
End If

End Sub

This code will execute only if any change of values or addition occurred and so we have defined the procedure as Worksheet_Change, Worksheet is the Object and Change is the Procedure.
The newitem contains the address of the cell in which we are adding the new values and we defined it as Range. The datatype of updatedrange and item would be considered as Variant, where we have assigned updatedrange to the updated named range of the vegetables and item is for the values of each cell of this range.
The FOR loop will assign the updated range to updatedrange and the WITH statement will avoid the repetition of the same object, and finally, we have added the validation.

Now, it’s time to return to the main sheet and check out the effect after the addition of a product Lettuce.
As we can see, we have this new item now in our dropdown list.

After selecting this new item we are having it in cell D6.

Read More: How to Use Data Validation List from Another Sheet (6 Methods)

Method-4: Using Named Range for Creating a Conditional Drop Down List

Here, we will create a dropdown list in cell E6 which will be dependent on the condition of cell D6 and for this reason, we have the following two named ranges, such as fruit1 and vegetable1.

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

Sub Datavalidation4()

If Range("D6") = "Fruits" Then
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=fruit1"
Else
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=vegetable1"
End If

End Sub

IF-THEN statement will check if the value in cell D6 is Fruits and for this value, as a list, we will get the named range fruit1 as a list in cell E6 otherwise we will get the named range vegetable1 as a list in cell E6.

➤ Press F5, then go to the worksheet and click on the dropdown symbol of cell E6.
After that, you will get the list of the fruits for the category as Fruits in cell D6 and select any one item from the list like the Blackberries.

So, we have got our desired item Blackberries from the list.

➤ For changing the category as Vegetables we can see that we are having the list of vegetables after running the code.

After selecting Broccoli we are getting this item in cell E6.

Related Content: How to Use IF Statement in Data Validation Formula in Excel (6 Ways)

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Conclusion

In this article, we tried to cover the ways to use a named range for the data validation list in Excel VBA easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• How to Use Data Validation in Excel with Color (4 Ways)
• Excel VBA to Create Data Validation List from Array
• How to Use Custom VLOOKUP Formula in Excel Data Validation
• [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
• How to Remove Blanks from Data Validation List in Excel (5 Methods)