[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 709
  • Last Modified:

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

0
RogueStat
Asked:
RogueStat
  • 2
  • 2
2 Solutions
 
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
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now