Create Dynamic Range Data Validation with Excel VBA

To create a dynamic range for data validation using VBA in Excel, you can use the following approach. This method allows you to set up a drop-down list in a cell where the range of values changes dynamically based on the data entered in a specified range.

Step-by-step Explanation:

Understanding the Goal: We want to create a dynamic drop-down list in Excel. For example, if the list of valid entries is in a column and the range of valid entries changes over time (e.g., adding or removing items), we want the data validation list to automatically adjust to reflect the new range without needing to manually update the validation.

Basic Approach: We can achieve this by defining a dynamic named range using VBA. This dynamic range will update automatically based on the number of entries in the list.

Dynamic Range: We will use a formula in the RefersTo property of the named range to dynamically adjust the range size. The formula will use the OFFSET function combined with COUNTA to calculate the number of non-empty cells.

Code Example:

Here’s an example of how to implement a dynamic data validation range using VBA:

Sub CreateDynamicValidation()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim validationRange As Range
    Dim dynamicRangeName As String   
    ' Set the worksheet and the validation range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Modify with your sheet name
    Set validationRange = ws.Range("A1") ' Modify with the cell where you want data validation 
    ' Define the dynamic named range
    dynamicRangeName = "DynamicList" ' Name of the dynamic range   
    ' Find the last row with data in column B (change B to your actual column)
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row   
    ' Create a dynamic range formula using OFFSET and COUNTA
    ThisWorkbook.Names.Add Name:=dynamicRangeName, RefersTo:="=OFFSET(Sheet1!$B$1, 0, 0, COUNTA(Sheet1!$B$1:$B$" & lastRow & "), 1)"   
    ' Apply data validation to the selected range
    With validationRange.Validation
        .Delete ' Remove any existing validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=DynamicList" ' Link the validation to the dynamic range
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Explanation of the Code:

1. Setting the Worksheet and Validation Range:

Set ws = ThisWorkbook.Sheets(« Sheet1 »)

Set validationRange = ws.Range(« A1 »)

1. • This sets the worksheet ws and the cell validationRange where you want the drop-down list to appear (in this case, cell A1).
2. Finding the Last Row:

lastRow = ws.Cells(ws.Rows.Count, « B »).End(xlUp).Row

• • This finds the last non-empty row in column B, where the data for the drop-down list is stored. You can change column « B » to any other column containing the list.

3. Creating the Dynamic Range:

Names.Add Name:=dynamicRangeName, RefersTo:= »=OFFSET(Sheet1!$B$1, 0, 0, COUNTA(Sheet1!$B$1:$B$ » & lastRow & « ), 1) »

• • This creates a dynamic named range (in this case, named DynamicList) using the OFFSET function. It starts at B1, then adjusts based on the number of non-empty rows in column B (calculated by COUNTA).
  • The OFFSET formula dynamically adjusts the range size as data is added or removed.

4. Setting the Data Validation:

With validationRange.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:= »=DynamicList »

.IgnoreBlank = True

.InCellDropdown = True

.ShowInput = True

.ShowError = True

• End With

1. • This sets up the data validation for the cell A1. It deletes any existing validation first, then adds a new validation list that points to the dynamic range DynamicList.

Notes:

• You can change the validationRange to apply the validation to multiple cells by changing Range(« A1 ») to a range like Range(« A1:A10 »).
• This code assumes the list of valid values is in column B. Adjust the column reference as needed.

By running this VBA code, any time you update the list in column B (by adding or removing values), the drop-down list in A1 will automatically adjust to reflect the new values without needing manual updates to the data validation range.

Conclusion:

This approach automates the process of creating dynamic drop-down lists in Excel, making it very useful for situations where the list of options is constantly changing.