remove duplicated from only data validation

[abdelfattah]

hello
I search so much in the internet how I can remove duplicated names from data validation as in my code data validation is g1
indeed I 'v found some solution but not perfect I have to filter the column f to remove duplicated but I need it I don't remove duplicated from column f b I would do that directly from data validation I no know if data validation Properties contains that
this is my code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(6)) Is Nothing Then
Dim Rng As Range
Set Rng = Range(Range("f1"), Range("f" & Rows.Count).End(xlUp))
With Range("g1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:="=" & Rng.Address & ""
End With
End If
End Sub

 

[abdelfattah]

any suggestion guys?

 

[Peter_SSs]

See if this does what you want

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim d As Object
  Dim a As Variant, itm As Variant
  
  If Not Intersect(Target, Columns(6)) Is Nothing Then
    Set d = CreateObject("Scripting.Dictionary")
    a = Range("F1", Range("F" & Rows.Count).End(xlUp)).Value
    For Each itm In a
      If Len(itm) > 0 Then d(itm) = 1
    Next itm
    With Range("G1").Validation
      .Delete
      .Add Type:=xlValidateList, Formula1:=Join(d.Keys, ",")
    End With
  End If
End Sub

 

[abdelfattah]

wow ! great work may you mod something ,please? I would show autocomplete for specific name when I write name in g1

if I have Ali, Ali1,Ali2 then show dropdown and show me the names based on write in g1 "ALI" show like this
ALI
AL1
AL2

 

[Peter_SSs]

Data Validation does not have an autocomplete feature. If you want something like that, then you would need to implement something like is described here

 

[abdelfattah]

so that's why I don' t find any thing about it in the internet
Any way , may you explain me which is line deletes duplicated items ? and if is possible to add some comments before lines to understand how the code works

 

[Peter_SSs]

may you explain me which is line deletes duplicated items ?

is possible to add some comments before lines to understand how the code works

Sure

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim d As Object
  Dim a As Variant, itm As Variant
  
  If Not Intersect(Target, Columns(6)) Is Nothing Then
    'Set up a 'dictionary' to store the DV values.
    'A dictionary does not allow multiple values the same, so it effectively eliminates the duplicates
    Set d = CreateObject("Scripting.Dictionary")
    
    'Read all the column values into an array (faster than processing worksheet cells individually)
    a = Range("F1", Range("F" & Rows.Count).End(xlUp)).Value
    
    'For each value in the array
    For Each itm In a
      'If there is an actual value, put it into the dictionary
      'As noted above the dictionary prohibits duplicates
      If Len(itm) > 0 Then d(itm) = 1
    Next itm
    
    'Now set up the DV
    With Range("G1").Validation
      .Delete
      'The DV values are obtained by joining all the dictionary values with commas between
      .Add Type:=xlValidateList, Formula1:=Join(d.Keys, ",")
    End With
  End If
End Sub

 

[abdelfattah]

thanks very much for your assistance

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[abdelfattah]

hi, peter

sorry I came back again I try mod your code to work for three data validation lists so my a new data in columns F,G,H and the dropdown lists in I,J,K I would use this code to work in my project , honestly I 'm comfortable with this code , do me favor how can I mod to works, please ?
I truly appreciate if you provide me assistance again

thanks again