Dropdown lists in Excel usually are built from a range or from a variable list.

Dropdown list from Excel Range

Thus, building a dynamic dropdown list may include building a dynamic range, with undeclared end. Thus, let’s imagine that the end of the range is unknown and it should be used for the dropdown list.

Thus, once we know the start, the end could be found with the LastRow  function:

Function LastRow(wsName As String, Optional columnToCheck As Long = 1) As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(wsName)
    LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row
End Function

The rest is easy – we should know which range to consider for the validation. Passing the range looks like this =C1:C5 and converted to a VBA formula, this looks  like the following:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
     Operator:=xlBetween, Formula1:="=" & ValidationRange.Address

The whole code looks like this:

Sub ValidationRangeAddress()

    Dim wks As Worksheet: Set wks = Worksheets(1)
    Dim endRow As Long: endRow = LastRow(wks.Name, 3)
    Dim ValidationRange As Range
    Set ValidationRange = wks.Range(wks.Cells(1, 3), wks.Cells(endRow, 3))

    With Worksheets(1).Cells(1, "A").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, _
                        Formula1:="=" & ValidationRange.Address
    End With

End Sub

Dropdown list from Named Range

Using named range for a source of a dropdown gives one huge advantage over using a normal range – if a new value is added to the range, the validation is automatically edited. Thus, we get quite dynamic range.

Sub ValidationNameRange()
    
    Dim wks As Worksheet: Set wks = Worksheets(1)
    Dim nameString As String
    Dim nameRange As Range
    
    nameString = "validator"
    Set nameRange = wks.Range("C1:C5")
    ThisWorkbook.Names.Add Name:=nameString, RefersTo:=nameRange
    
    With Worksheets(1).Cells(1, "A").validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, _
                        Formula1:="=" & nameRange.Address
    End With

End Sub

Dropdown list from variable list

Using a range for a source for the dropdown in general has 1 drawback – we should write the range somewhere in Excel. And sometimes, when the Excel file becomes too large, it is not always handy to look for the “Settings” tab and try to edit it back. Thus, it is possible to write the range from a  list, which is simply inserted through VBA. In the example below, there is a folder “QA” in the desktop, accessed through Environ(“UserProfile”) & “\Desktop\QA”. In this folder, there are a few *.xlsx files, which are converted through a dropdown. Without writing them to the Excel file.

Sub DropdownList()
    
    Dim filePath As String
    filePath = Environ("UserProfile") & "\Desktop\QA"
    Dim fsoLibrary As Object: Set fsoLibrary = CreateObject("Scripting.FileSystemObject")
    Dim fsoFolder As Object: Set fsoFolder = fsoLibrary.GetFolder(filePath)
    Dim fsoFile As Object

    Dim validationString As String
    For Each fsoFile In fsoFolder.Files
        If fsoFile Like "*.xlsx" Then
            validationString = validationString & fsoFile.Name & ", "
        End If
    Next fsoFile
    
    If validationString <> "" Then
        validationString = Left(validationString, Len(validationString) - 2)
        With Worksheets(1).Range("A1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:=validationString
        End With
    End If

End Sub

Thank you for reading! 🙂