Data Validation with Multiple Dynamic Ranges with VBA - Macro to Auto-Update

[jsticca]

Hello,

I currently have a workbook with a tab named "Fill Form Fields", within that tab are multiple dynamic ranges (which are named) in columns of data that I have compiled into a data validation list in a cell on another tab named "iDiR Repairs" through the use of a macro.

My problem is, if I add additional data to the ranges on the "Fill Form Fields" tab, the data validation cell does not update. For some reason the dynamic nature of the range names is lost when I run the macro.

• Is there some way to automate this macro to continually update when the ranges are expanded?
• I would like to be able to drag down the data validation cell and auto populate below it, with all of the cells auto-updating if I expand the ranges on the "Fill Form Fields" tab. Is this possible?

Here are the dynamic range functions for the Fill Form Field tabs, named Inventory1, Inventory2, and Inventory3 respectively:

=OFFSET('Free Form Fields'!$B$2,0,0,COUNTA('Free Form Fields'!$B:$B),1)
=OFFSET('Free Form Fields'!$D$2,0,0,COUNTA('Free Form Fields'!$D:$D),1)
=OFFSET('Free Form Fields'!$F$2,0,0,COUNTA('Free Form Fields'!$F:$F),1)

Here is the VBA code to create the data validation cell on the "iDiR Repairs" tab that consolidates each range into one drop down:

Private Sub Validation_multiple_ranges()    Dim a, el As Range
    Dim rng1 As Range, rng2 As Range, Inventory As Range
    
        Set rng1 = Range("Inventory1") 'you can assign by namerange
        Set rng2 = Range("Inventory2")
        Set rng3 = Range("Inventory3")
        
     For Each el In rng1
        a = a & el.Value & ","
     Next
     For Each el In rng2
        a = a & el.Value & ","
     Next
     For Each el In rng3
     a = a & el.Value & ","
     Next
     With Sheets("iDiR Repairs").Range("A1").Validation 'destination val.list
        .Delete
        .Add Type:=xlValidateList, Formula1:=a
    End With
        Set rng1 = Nothing
        Set rng2 = Nothing
        Set rng3 = Nothing
End Sub

Thanks!