[VBA] Losing my mind trying to simply add a range to drop down list

[RockandGrohl]

Don't know why I'm finding this so difficult.

In Sheet "Data" I have a dynamic-length list from X2 downwards.

I want to add a data validation list box to K9 which contains this list. The contents & length of the list are dynamic.


Right now I'm doing this:

Dim TourList As Range
LastrowAD = Cells(Rows.Count, "A").End(xlUp).Row

...

Range("W2:W" & LastrowAD).Value = Range("W2:W" & LastrowAD).Value
        Columns("W:W").Copy
        Range("X1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        ActiveSheet.Range("X1:X" & LastrowAD).RemoveDuplicates Columns:=1, Header:=xlNo
        ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("X2:X" & Lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Data").Sort
            .SetRange Range("X1:X" & LastrowAD)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Range("X2").Activate
    Set TourList = Range("X2:X" & Cells(Rows.Count, "X").End(xlUp).Row)

The above takes the first character of column A and places it in column W, then gets a de-duped list in column X and sorts it A-Z. Finally, it makes that de-duped and sorted range a "Range" object (which for this instance is X2:X10)

Finally, I want to reference this list in K9:

With Range("K9").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=TourList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

This then gives me Application-defined or Object defined error. The weirdest thing is that I seem to be on the right path according to here:

Add values to a regular drop-down list programmatically

Table of Contents Add values to a regular drop-down list programmatically How to insert a regular drop-down list Add values to drop-down list progr

www.get-digital-help.com

Thank you!

 

[Fluff]

How about

Formula1:="=tourlist"

 

[RockandGrohl]

How about

Formula1:="=tourlist"

....

So if I have it like this:

Set TourList = Range("X2:X" & Cells(Rows.Count, "X").End(xlUp).Row)

And then write:

With Range("K9").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=TourList"

I still get "Application defined or Object-defined error"

 

[Domenic]

Try...

Formula1:="=" & TourList.Address(external:=True)

Hope this helps!

 

[Fluff]

Oops, I thought "TourList" was a named range, try it like

Range("X2:X" & Cells(Rows.Count, "X").End(xlUp).Row).Name = "TourList"

With Range("K9").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=TourList"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

 

[RockandGrohl]

Thanks guys, both solutions work here. Much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback