Blanks in dropdownlist

[oskr989]

Hi!

I would like to do a drop-down list that ignores blank cell in a range, here is an example

Excel Workbook
	A
1	Range1
2
3	Test1
4
5
6	Test2
7
8
9	Test3
10
...

Here is the example of what the dropdown list should look like:

Hope you can help me, thanks.

 

[Armando Montes]

Try this code:

Private Sub Worksheet_Activate()
Dim r As Range, txt As String
For Each r In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If Not IsEmpty(r) Then txt = txt & "," & r.Value
Next
With Range("C1")
    .Value = Empty
    With .Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Mid$(txt, 2)
    End With
    .Select
End With
End Sub