Create Dynamic Range Validation with Excel VBA

This code will:

• Define a named range dynamically – The list of values for validation will automatically adjust as items are added or removed.
• Apply data validation to a target range – This ensures that users can only select values from the defined list.
• Handle updates dynamically – Whenever new data is added, the validation updates automatically.

Step 1: Understanding the Dynamic Named Range

A dynamic named range in Excel adjusts automatically when new data is added or removed. We can create it using:

• OFFSET function: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
• INDEX function: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

The VBA code below will:

• Create a named range using Formulas.
• Apply data validation to a selected range.

Step 2: VBA Code Implementation

Sub CreateDynamicValidation()
    Dim ws As Worksheet
    Dim rngSource As Range
    Dim rngTarget As Range
    Dim lastRow As Long
    Dim nameDefined As String
    Dim validationFormula As String
    ' Set the worksheet where the list is stored
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row with data in column A (source list)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Define the source range dynamically
    Set rngSource = ws.Range("A1:A" & lastRow)
    ' Define a name for the dynamic range (Modify if necessary)
    nameDefined = "DynamicList"
    ' Delete existing name if it already exists
    On Error Resume Next
    ThisWorkbook.Names(nameDefined).Delete
    On Error GoTo 0
    ' Create a named range dynamically
    ThisWorkbook.Names.Add Name:=nameDefined, RefersTo:="=" & ws.Name & "!$A$1:INDEX(" & ws.Name & "!$A:$A,COUNTA(" & ws.Name & "!$A:$A))"
    ' Set the target range where validation should be applied (Change as needed)
    Set rngTarget = ws.Range("C2:C20") ' Modify range accordingly
    ' Apply Data Validation using the dynamic named range
    With rngTarget.Validation
        .Delete ' Remove existing validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & nameDefined
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    MsgBox "Dynamic data validation applied successfully!", vbInformation, "Success"
End Sub

Step 3: Explanation of the Code

1. Worksheet and Source Range Selection

Set ws = ThisWorkbook.Sheets(« Sheet1 »)

1. • Specifies the worksheet where the source list is stored.
2. Find the Last Row in Column A

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• • Identifies the last used row in column A to determine the dynamic range.

3. Define and Create a Dynamic Named Range

Names.Add Name:=nameDefined, RefersTo:= »= » & ws.Name & « !$A$1:INDEX( » & ws.Name & « !$A:$A,COUNTA( » & ws.Name & « !$A:$A)) »

• • Uses INDEX and COUNTA to define a dynamic named range that grows or shrinks as data changes.

3. Select Target Cells for Validation

Set rngTarget = ws.Range(« C2:C20 »)

• • Specifies where the validation should be applied (column C, rows 2 to 20 in this case).

3. Apply Data Validation

• With rngTarget.Validation

.Delete ‘ Remove existing validation

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:= »= » & nameDefined

• • Removes any previous validation.
  • Adds list validation, ensuring users can only select values from DynamicList.

Step 4: Running the Code

• Open Excel and create a sheet named Sheet1.
• In Column A, enter a list of values (e.g., Apple, Banana, Orange).
• Run the VBA macro.
• Try selecting a value in column C2:C20 – it should only allow values from column A.

Step 5: Making It More Dynamic

• Instead of setting a fixed range (C2:C20), use:

• Set rngTarget = ws.Range(« C:C »)

• • This applies validation to the entire column C dynamically.
• Instead of hardcoding « Sheet1 », allow users to select a sheet:

• Set ws = ActiveSheet

• • This allows the macro to work on any active sheet.

Conclusion

This VBA code dynamically manages data validation by:

Automatically updating when the source list changes
Using a named range for better flexibility
Applying validation to any specified target range