VBA to create validation List & Ignore duplicates

**jordan2322** · 04-04-2012, 06:54 PM

Hi guys,

I want to create a drop down style Validation List from the following data set

Col G
row 20: OH
row 21: OH
row 22: OH
row 23: OH
row 24: UG
row 25: UG
row 26: SV

However I do not wish for the duplicate entries to be allowed. ie from the example above the list should have as selectable options: OH, UG, SV

The used range for these list varies - the list should be dynamic such that it will work until the next blank space is found in column G - perhaps using excel down function.

kind regards

**JieJenn** · 04-04-2012, 07:19 PM

Assuming your list is stored in Column A, just enter the code below in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim ValUnique As New Collection
Dim rStr As String
Dim i As Long


Application.Volatile
On Error Resume Next
For Each Cell In Range("A1:A7")
    ValUnique.Add Cell.Value, CStr(Cell.Value)
Next Cell

    For i = 1 To ValUnique.Count
        rStr = rStr & "," & ValUnique.Item(i)
    Next i
    
    rStr = Right(rStr, Len(rStr) - 1)
    Debug.Print rStr
    
    With Range("C1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=rStr
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = False
    End With
End Sub

**jordan2322** · 05-28-2012, 12:58 AM

Is there a way we can tell this to ignore specific Values. I.e. Ignore all numerical values found in the range.

Or - could we set it up to only display those values that appear exactly 1 row below cells in column A that have a Top Border.

Regards

**mterhaar** · 06-15-2013, 10:52 AM

Hi There,

The above code and workbook is exactly the solution I have been looking for. I imagine this is a silly questions but I am having trouble getting the data validation list to a seperate worksheet. (always appears in C1)

Any ideas?