Getting dynamic dropdown list in VBA validation

I found this one interesting, so check this out... Should work as you expect it... Post this code into your Worksheet and adapt it for your needs (if necessary). Hope it helps.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim dict As Object
    Dim dictAlreadyTaken As Object
    Dim valueRange As Range
    Dim targetRange As Range
    Dim cell As Object
    Dim Key As Variant
    Dim currentList() As Variant
    Dim i As Integer

    If Target.Column = 1 Then
        Set ws = Worksheets(1)
        Set dict = CreateObject("Scripting.Dictionary")
        Set dictAlreadyTaken = CreateObject("Scripting.Dictionary")
        Set valueRange = ws.Range("D:D")
        Set targetRange = ws.Range("A:A")

        For Each cell In valueRange
            If cell.Value <> "" Then
                dict.Add cell.Value, cell.Row
            Else
                Exit For
            End If
        Next cell

        For Each cell In targetRange
            If cell.Row <= dict.Count Then
                If cell.Value <> "" Then
                    'ad the value taken
                    dictAlreadyTaken.Add cell.Value, cell.Row
                End If
            Else
                Exit For
            End If
        Next cell

        For Each cell In targetRange
            If cell.Row <= dict.Count Then
                'add this list
                Erase currentList
                ReDim currentList(0)
                i = 0
                ws.Cells(cell.Row, 1).Validation.Delete
                For Each Key In dict.keys
                    If Not dictAlreadyTaken.exists(Key) Then
                        i = i + 1
                        ReDim Preserve currentList(i) As Variant
                        currentList(i) = Key
                    End If
                Next Key
                If UBound(currentList) > 0 Then
                    ws.Cells(cell.Row, 1).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(currentList, ",")
                End If
            Else
                Exit For
            End If
        Next cell
    End If
End Sub

My thought process would be:

1. First loop to list all the ranges we need to compare:

   • Cells(1,1) should not appear in Range(Cells(1,4),Cells(1,4))

   • Cells(2,1) should not appear in Range(Cells(1,4),Cells(2,4))

   • Cells(3,1) should not appear in Range(Cells(1,4),Cells(3,4)) ...etc...

2. Easy enough. Now that we know what ranges to compare, loop through the comparisons:

   • re: Cells(3,1) should not appear in Range(Cells(1,4),Cells(3,4)) :

.

Dim c as range
For Each c in Range(Cells(1,4),Cells(3,4))
If c.Value = Cells(1,4).Value then
    'it's a match! Delete it (or whatever)
    c.Value = ""
End If
Next c

Finally, put the two loops together...

From what I understand of your description, I came up with this:

Sub compareCells()

    Dim c As Range, x As Integer
    For x = 1 To 10
        Debug.Print "Cells(" & x & ",1) should not appear in Range(Cells(1,4),Cells(" & x & ",4))"

        For Each c In Range(Cells(1, 4), Cells(x, 4))

            Debug.Print "compare " & Cells(x, 1).Address & " to " & c.Address

            If Cells(x, 1).Value = c.Value Then
                Cells(x, 1).Cells.Font.Color = vbBlue
            End If

        Next c
    Next x

End Sub

It should be easily adaptable to your needs, or if not, there are plenty of existing solutions & resources, even a Stack Overflow tag: cascadingdropdown

Here is an approach:

Select a column in your sheet that you can use for a named range (this column can be hidden). For the purpose of example below, I've used column J and my named range is called ValidationRange. I have also assumed that the values in your worksheet start from row 2.

Now in a module, add the following sub:

Sub SetDropDownRange()

    Dim oNa As Name: Set oNa = ThisWorkbook.Names.Item("ValidationRange")
    Dim iLR&, iC&, iLRJ&
    Dim aDRange As Variant
    Dim aVRRange As Variant

    With ThisWorkbook.Worksheets("Sheet12")
        iLR = .Range("D" & .Rows.count).End(xlUp).Row
        iLRJ = .Range("J" & .Rows.count).End(xlUp).Row

        aDRange = Range("D2:D" & iLR)

        For iC = LBound(aDRange) To UBound(aDRange)

            If Len(Trim(aDRange(iC, 1))) <> 0 Then

                If Application.WorksheetFunction.CountIf(Range("A:A"), aDRange(iC, 1)) = 0 Then

                    If IsArray(aVRRange) Then
                        ReDim Preserve aVRRange(UBound(aVRRange) + 1)
                    Else
                        ReDim aVRRange(0)
                    End If

                    aVRRange(UBound(aVRRange)) = aDRange(iC, 1)

                End If

            End If

        Next

    End With

    Range("J2:J" & iLRJ).Value = ""

    Range("J2:J" & UBound(aVRRange) + 2).Value = Application.Transpose(aVRRange)

    oNa.RefersTo = oNa.RefersToRange.Resize(UBound(aVRRange) + 1, 1)

End Sub

Now call this function when something changes in your worksheet.. like so:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Or Target.Column = 4 Then
        SetDropDownRange
    End If
End Sub

Set Data Validation for the cells in column A using the named range (which is ValidationRange for this example)

Now everytime your select a value in column A, it will remove that value from the named range and hence from your dropdown box