Re: Function to Ignore duplicates in a data validation list

Discussion in 'Excel Worksheets' started by Debra Dalgleish, Apr 2, 2004.

  1. To do this dynamically, you could use an event procedure. For example,
    if your list of cost centres is in C2:C20, the following code will copy
    the unique values to a sheet named Lists, sort the list, and name it,
    when a cell in column C is selected.

    The code should be pasted onto the module for the sheet with the data
    validation. Right-click the sheet tab, choose View Code, and paste the
    code onto the code module, where the cursor is flashing.

    '==================================
    Private Sub Worksheet_SelectionChange _
    (ByVal Target As Range)
    Dim r As Long
    Dim wsL As Worksheet
    Set wsL = Worksheets("Lists")
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 3 And Target.Row > 2 Then
    wsL.Range("A1").CurrentRegion.ClearContents
    Range(Cells(2, 3), Cells(Target.Row - 1, 3)) _
    .AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=wsL.Range("A1"), Unique:=True
    r = wsL.Cells(Rows.Count, 1).End(xlUp).Row
    wsL.Range(wsL.Cells(1, 1), wsL.Cells(r, 1)).Sort _
    Key1:=wsL.Range("A1"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    ActiveWorkbook.Names.Add Name:="DataValList", _
    RefersToR1C1:="=Lists!R1C1:R" & r & "C1"
    With Target.Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, _
    Formula1:="=DataValList"
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowError = False
    End With
    End If
    End Sub
    '===========================

    Kevin Wickersheim wrote:
    > I have a sheet where the user will enter peoples names and cost centers. Below this I have set a cell with Data>Validation>list and the ranges is the cost centers from the people that where entered. Is there a way to get this drop down list to ignore duplicate cost center?



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html
    Debra Dalgleish, Apr 2, 2004
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Galceran
    Replies:
    0
    Views:
    51
    Galceran
    Oct 30, 2007
  2. Peter
    Replies:
    1
    Views:
    69
    T. Valko
    Sep 22, 2009
  3. D Lu
    Replies:
    4
    Views:
    35
  4. Nuclear
    Replies:
    2
    Views:
    47
    M Kan
    Jul 29, 2008
  5. jd_wright

    Spelling and "Ignore"/"Ignore All"

    jd_wright, Jan 31, 2010, in forum: Mac Office Word
    Replies:
    7
    Views:
    148
    jd_wright
    Feb 2, 2010
Loading...

Share This Page