Populate Dropdown with Unique Values

Hey guys,
I have a named range in excel.  I'm used to filtering for unique values in access using SQL but here I am stumped.  I need to populate a dropdown with a  list of dates.  The dates are all in one column, but the date can be repeated hundreds of times.  Right now I just have a range that lists every date in the column.

There has to be some good way to filter this for unique values.  I tried using AdvancedFilter but couldn't make it work.  Is there a way I can use this or should I do some kind of loop?

Every date is in column A of sheet1:

Date
1/1/2010
1/1/2010
1/2/2010
1/3/2010
1/3/2010
1/3/2010
1/3/2010 etc


Dim RDates as Range
Dim RDates2 as Range

Set RDates = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown))

'This errors out, not sure what I am doing wrong
RDates.AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=RDates2, Unique:=True

Open in new window

LVL 1
RogueStatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sstampfCommented:
Use this code:
Dim RDates As Range
Dim RDates2 As Range

Set RDates = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown))
'I have added this part assuming you want to paste the data at Cell C1
Set RDates2 = Sheets("Sheet1").Range("C1")


RDates.AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=RDates2, Unique:=True

Open in new window

0
RogueStatAuthor Commented:
Thanks, that got it working, i'm not sure what I was doing wrong, I thought for sure I tried that very thing earlier!  Thanks for the assistance.

One follow-up:  Is it possible to create a drop-down box based on a range or array that exists only in VB?  For example, if instead of using the code above, I somehow put the unique values into a vba array and then did some manipulation.  Is there any way to populate a box with these values without pasting them directly onto a sheet somewhere?

If it's not possible (or easy) it's not a big deal but I was curious.
0
Saurabh Singh TeotiaCommented:
One quick question when you mean drop down you meant data validation..?? and if that whats you meant then use the following code..
And i assumed you want to show the validation on B1..
Saurabh...

Dim rng As Range, srow As Long, lrow As Long
    Dim str As String
    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    srow = 2

    Do Until srow > lrow
        Set rng = Range("A2:A" & srow)

        If Application.WorksheetFunction.CountIf(rng, Cells(srow, "a").Value) = 1 Then
            If str = "" Then
                str = Cells(srow, "a").Value
            Else
                str = str & "," & Cells(srow, "A").Value
            End If
        End If
        srow = srow + 1
    Loop

   With Range("B1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:=str
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sstampfCommented:
I have modified Saurabh's code a bit. If you have stored your unique values into a VBA array then you can use/modify the code below to suit your needs. This way you don't have to store anything at all on the worksheet.
Sub test()
Dim arr(5) As Date
Dim str As String
arr(0) = #1/1/2009#
arr(1) = #2/1/2008#
arr(2) = #3/1/2009#
arr(3) = #4/1/2008#
arr(4) = #5/1/2009#
str = arr(0)
For i = 1 To 4
    str = str & "," & arr(i)
Next i
With Range("B1").Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
          xlBetween, Formula1:=str
     .IgnoreBlank = True
     .InCellDropdown = True
     .InputTitle = ""
     .ErrorTitle = ""
     .InputMessage = ""
     .ErrorMessage = ""
     .ShowInput = True
     .ShowError = True
 End With
End Sub

Open in new window

0
RogueStatAuthor Commented:
Thank you guys, this looks really good.  I was hoping for something that doesn't store values, I really appreciate the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.