Create data validation from comma separated values

Need to create dropdown list from comma separated values. The range can be anything. Comma separated values will be coming from database which I have placed in the position of AD column. So I want to convert those values as drop down list in R column. Can anyone help me out. Using Vba.
vanmathi arumugamAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this. Tweak it if required as per your requirement.
Sub CreateDropDownList()
Dim lr As Long, i As Long
Dim x
Dim str As String
lr = Cells(Rows.Count, "AD").End(xlUp).Row
If lr < 2 Then Exit Sub
x = Range("AD1:AD" & lr).Value
For i = 1 To UBound(x, 1)
    If str = "" Then
        str = x(i, 1)
    Else
        str = str & "," & x(i, 1)
    End If
Next i
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With Range("R2:R" & lr).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=str
End With
End Sub

Open in new window

1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Answered the original question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.