none
Excel VBA code to create a drop list for all cells in a column. RRS feed

  • Question

  • Hello everyone. I am trying to make a code to create a dropdown list for all cells filled in a column. I used the macro recorder to figure out how to make a code that takes a range of cells and makes a dropdown list in another sheet. Now what I would like to do is have it create a dropdown list from all cells filled out in a column that way if someone adds a name it will be added to the dropdown list. Does anyone know how I might do this? Any help would be greatly appreciated. Here is the code for a dropdown in a range that I have used.

    Range(Range("B10"), Range("B" & Rows.Count)).Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$B$10:$B12"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        Sheets("RAIL").Select
        Range(Range("B7"), Range("B" & Rows.Count)).Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="='Team Members'!$B$10:$B$12"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With

    Monday, March 14, 2016 12:37 AM

All replies

  • You don't need VBA to do that - use a defined name, like   "List"  where List is defined as

    =OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)

    And set your DV to =List

    As items are added to the bottom, the List will expand to match. If your list has a header in cell B1 that you don't want to include, change the definition to a formula like this:

    =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

    Monday, March 14, 2016 6:15 PM
  • Thank you very much Bernie.

    I may use this instead that is very good to know.

    However, there is an update button that does other things and I am trying to learn VBA coding so I may try to see if there is a way to put that into a code.

    Thanks again!

    Monday, March 14, 2016 8:14 PM