VBA: Validate List settings

Question

Sub Macro1()
'
' Macro1 Macro
'

'
    Worksheets("Drop-down").Select
    For i = 1 To 10
        ActiveSheet.Cells(i, 2).Select
        With Selection.Validation
            .Delete
            ' Error in this line
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End)
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    Next i
End Sub

I'm getting an error in the line below the comment, yet I don't know how to fix it. It would be great to hear some suggestions. Thanks in advance!

Answer 1

There are two issues; the first is you are missing a required argument for the End function, which is a named Excel constant to denote direction.

Instead of Worksheets("Misc").Cells(2, i).End on that line try Worksheets("Misc").Cells(2, i).End(xlDown)

You can use xlLeft, right or up as well, but I'm guessing down is what you are looking for.

Second issue is the value for Formula1 has to be a string address (I think) and you are passing an actual range object.

Try this for that line

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End(xlDown)).Address

This worked on my machine, without errors. I of course stubbed in dummy data, so I don't know if it is correct for your worksheet

EDIT (in response to Stan's comment)

There may be a sexier way, but this is a good and simple (which is sexy too, in it's own right); the if statement just checks the cell in the following row. If its non-empty, then it proceeds with the same solution you had before. If it's empty, then it uses just that one cell as the validation range.

For i = 1 To 10
    If Worksheets("Misc").Cells(3, i).Value <> "" Then
        Set validationRange = Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End(xlDown))
    Else
        Set validationRange = Worksheets("Misc").Cells(2, i)
    End If

    With Worksheets("Drop-down").Cells(i, 2).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ 
                    Formula1:=validationRange.Address.......

I combined your

Worksheet.Selection
Selection.DoStuff

pairs, into

Worksheet.DoStuff

I think it looks a little nicer.

Let me know if this works for ya!