Dropdown List for unknown range

  • I'm trying to define a Dropdown List for a Column range.


    This works fine if I know the range


    Code
    1. ActiveWorkbook.Names.Add Name:="Names", RefersTo:="=Menus!$A$2:$A$29"


    but not always the rage is the same, is always the same column, it always starts on "A2" but don't know where it finishes


    maybe a Loop would do it, but I still don't know how to do it :( can you guys give a hand here?


    Thanks a lot.

  • Re: Dropdown List for unknown range


    Code
    1. Dim MyRange as Range
    2. Set MyRange=Range("A2",Range("A65536").End(xlUp))


    That will set the range each time your code is run. Are you using a UserForm?
    If you are use

    Code
    1. ComboBox1.RowSource=Range("A2",Range("A65536").End(xlUp)).Address
  • Re: Dropdown List for unknown range


    Hi RoyUK, thanks for your reply,


    I'm not using a UserForm.


    the set range was what I needed, just one thing, how do I replace the range in the RefersTo?


    Code
    1. ActiveWorkbook.Names.Add Name:="Names", RefersTo:="=Menus!MyRange"
    2. or
    3. ActiveWorkbook.Names.Add Name:="Names", RefersTo:="=Menus!(MyRange)"


    doesn't work

  • Re: Dropdown List for unknown range


    If I do


    Code
    1. Set ListRange = Range("A2", Range("A65536").End(xlUp))
    2. ActiveWorkbook.Names.Add Name:="Names", RefersTo:=ListRange


    it works, but sets the range on the 1st sheet, how do I make it to read the range on a diferent sheet?

  • Re: Dropdown List for unknown range


    Code
    1. Set ListRange = Sheet2.Range("A2", Range("A65536").End(xlUp))
    2. ActiveWorkbook.Names.Add Name:="Names", RefersTo:=ListRange


    Alter the sheet number as required


    You could consider a Dynamic named Range for a non-VBA solution.

  • Re: Dropdown List for unknown range


    With



    and any sheet number, I get


    Run-time error '1004':
    Method 'Range' of object '_Worksheet' failed


    any idea with what is wrong?

  • Re: Dropdown List for unknown range


    Try activating the sheet first, so