Excel extract all data of drop down list into Column

Question

I have copied a drop down list cell from other sheet and trying to fetch its all items name. Dropdown list is created through excel property by this link, not using Combo Box. Is there any way to get all items of drop down list ? Means get the original list which is mapped to the drop down.

I tried to get it through VB Script also but it does not work.

Dim dd As DropDown
Set dd = ActiveSheet.DropDowns("MyDropDown")

When I debug that code it show "unable to get the dropdowns property of the worksheet class" error at

Set dd = ActiveSheet.DropDowns("MyDropDown")

line and this Excel dropdowns in VBA: "Unable to get the DropDowns property of the Worksheet class" not helped me. Is ActiveSheet.DropDowns("MyDropDown") works only for Combo Box ? Then how I get all items either using excel property or VB Script?

Scott Marcus Scott Marcus · Accepted Answer · 2016-02-19T15:20:04

Ah, your original question didn't make it clear that you were using Data Validation. To loop through all the items in a Data Validation list, you can use this code:

Sub loopthroughvalidationlist()
     Dim inputRange As Range
     Dim c As Range
     ' Change range below to first cell in your list
     Set inputRange = Evaluate(Range("J6").Validation.Formula1)
     For Each c In inputRange
        MsgBox (c)
     Next c
End Sub

Excel extract all data of drop down list into Column

1 Answers