Search This Blog

Monday 23 July 2012

CREATE DATA VALIDATION LIST WITH UNIQUE VALUES FROM A LIST OF DUPLICATES

Paste the below code in module and run it.


Option Explicit

Sub data_validation_with_unique_values()
Dim clctn As New Collection
Dim arr As Variant
Dim i As Integer
Dim distinct() As String

'Fill values from your range into array
arr = Application.Transpose(Sheet1.Cells(1, 1).CurrentRegion.Resize(, 1).Value)

'Create a list of unique
On Error Resume Next
For i = LBound(arr) To UBound(arr)
clctn.Add arr(i), arr(i)
Next i
On Error GoTo 0

ReDim distinct(1 To clctn.Count)
For i = 1 To clctn.Count
distinct(i) = clctn(i)
Next

'Paste unique values in column E
Sheet1.Cells(1, 5).Resize(clctn.Count).Value = Application.Transpose(distinct)

'Give a name range to your list in column E
Range("E1:E" & Range("E65536").End(xlUp).Row).Name = "Myrange"

'Create data validation list in active cell using named range
ActiveCell.Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "=Myrange"

End Sub

Note: Change the highlighted part as per your requirement.

2 comments:

  1. Hi Shweta
    Earlier, this afternoon I sent you a message. But I doubt about message procedure. So I sent you my question again:
    I copied your VBA code for a Data Validation with Unique Values. but I have an error on row: 'ReDim distinct(....)'
    So, I would ask you, please will you send me a Excel model as Example.
    I thank you very much for your answer.
    With regards,
    Bert van Zandbergen, Beekbergen The Netherlands
    Email: klvzndbrgn@gmail.com

    ReplyDelete
  2. Thank you for this helpful tip. I just wanted you to know that it was very helpful to me to use this in my project to create a list of unique values which are then used in a dynamic named range as a drop down menu to then be used as a report filter.

    ReplyDelete