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.
Hi Shweta
ReplyDeleteEarlier, 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
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