VBA macro to create drop down list from selected cells [Excel]

Simple VBA macro to create drop down list from selected cells in your excel sheet.

1. Select the cells for which you want to create drop down list
2. Run the macro. Drop down list gets created in column next to the selected cells (with down arrow)
3. Click on down-arrow button to see the list

Notes: You can copy & paste the drop-down list cell (2nd step) anywhere in your sheet.But, if you delete the data from selected cells(1st step), you won’t see data in drop down list. For example, if you delete item1, you will see only item2,item3 and item4 in the drop down list.

VBA macro to create drop down list

Sub createDropDownList()
 Set SelRange = Selection
 ActiveWorkbook.Names.Add Name:="dropDownName", RefersToR1C1:=SelRange
 ActiveWorkbook.Names("dropDownName").Comment = ""
 ActiveCell.Offset(0, 1).Value = "drop-down"
 ActiveCell.Offset(0, 1).Select
 With Selection.Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
 xlBetween, Formula1:="=dropDownName"
 .IgnoreBlank = True
 .InCellDropdown = True
 .InputTitle = ""
 .ErrorTitle = ""
 .InputMessage = ""
 .ErrorMessage = ""
 .ShowInput = True
 .ShowError = True
 End With
End Sub