1

I want to create a drop-down list in Cell E5.

My range of values for the drop-down should come from Range(A1:A5) in Sheet2 and Range(C1:C7) in Sheet2. However, my code below is not working, it runs an error 1004 at Add xlValidateList, xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet2!$A$1:$A$5 & Sheet2!$C$1:$C$7" I suspect it has something to do with this line...Formula1:="=Sheet2!$A$1:$A$5 & Sheet2!$C$1:$C$7" . Does anyone know how I can add two ranges into my Formula1?

Also, I dont want the blanks in the ranges to appear in the Drop-down list but .IgnoreBlank = True still shows the blanks in the drop-down list. This is my code thus far, any help is genuinely appreciated:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("E5")) Is Nothing Then

With Range("e5").Validation
.Add xlValidateList, xlValidAlertStop, Operator:=xlBetween, Formula1:="=Sheet2!$A$1:$A$5 & Sheet2!$C$1:$C$7"
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = "Warning"
.ErrorMessage = "Please select a value from the drop-down list available."
.ShowError = True

End With

End If

End Sub
Improve this question
3
  • 3
    To add multiple columns data, use a unique collection and then feed that to the DV Commented Aug 13, 2019 at 15:31
  • hi @SiddharthRout can I get a hint on how I should start on that? So sorry Commented Aug 13, 2019 at 15:33
  • 1
    I posted an answer. You may have to refresh the page to see it. I have commented the code so you should not have a problem understanding it. Still if you do then simply ask :) Commented Aug 13, 2019 at 15:42

2 Answers 2

Reset to default
1

To add multiple columns data, use a unique collection and then feed that to the DV in a comma delimited string. See this example. Change as applicable.

Sub Sample()
    Dim col As New Collection
    Dim rng As Range
    Dim i As Long
    Dim DVList As String

    '~~> Loop through the data range
    For Each rng In Sheet2.Range("A1:A5,C1:C5")
        '~~> Ignore blanks
        If Len(Trim(rng.Value)) <> 0 Then
            '~~> Create a unique list
            On Error Resume Next
            col.Add rng.Value, CStr(rng.Value)
            On Error GoTo 0
        End If
    Next rng

    '~~> Concatenate with "," as delimiter
    For i = 1 To col.Count
        DVList = DVList & col.Item(i) & ","
    Next i

    '~~> Feed it to the DV
    With Sheet1.Range("E5").Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Formula1:=DVList
    End With
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you so much, this work so simply well.....you're a genius!! Can I just ask...what does the portion '~~> Concatenate with "," as delimiter mean? I dont get that line even after your comment .. ><
when you manually create a DV list, you can enter a,b,c,d? So the code is doing that. It is passing 1,2,3,4...
ah.... so sorry..., so it is merely counting the no. of items in the cols?
No it is joining then with a comma
0

the property Formula1:="Expression" does not allow splitted tables. As Siddharth Rout wrote - as a work aorund - first you have to collect all values in a single table or a string.

Also the IgnoreBlank property does not ignore blanks, but is considering the cell value as valid even the cell content is blank. So, while collecting table content skip blank cells.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.