Dropdown list in a range with last availabe column and row using vba

[rollis13]

Sure, I was following your example.

If the sheet contains only the input range and the Validation List placed in another sheet and refered to as a NamedRange you could use:

Option Explicit

Sub Add_Drop_Down()

    Dim LCell As String
    LCell = ActiveCell.SpecialCells(xlLastCell).Address

    With Range("C4:" & LCell).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Validation_List"
        .IgnoreBlank = True
        .InCellDropdown = True
    End With

End Sub