Excel VBA Create Dynamic Unique & Sorted Data Validation List

Excel VBA Dynamic Data Validation List Contains Unique And Alphabetical Sorted Values

        In order to use data validation lists more efficiently, using VBA codes we created lists that unique values and listed these values alphabetically.

excel data validation list unique sorted values

         In the workbook; There are two sheets named "Sheet1" and "Products". The data are got from column "B" of the Products sheet and listed the data by creating Data Validation Lists on between the 2nd and 41th rows in the column "A" of Sheet1 .
With the VBA For-Next loop, we added our alphabetically ordered data validation lists containing unique values ​​to cells A2: A41 of Sheet1.

Our VBA codes :
Sub Benzersiz_Alfabetik_Liste()
Dim dizi As Object, Veri As Range, Son As Long, x As Byte
Application.ScreenUpdating = False
Application.EnableEvents = False

    On Error Resume Next
     Set dizi = CreateObject("System.Collections.ArrayList")
    Son = Sheets("Products").Cells(Rows.Count, 2).End(3).Row
   For Each Veri In Sheets("Products").Range("B2:B" & Son)
    If dizi.Contains(Replace(Veri.Value, ",", ".")) = False Then
    dizi.Add (Replace(Veri.Value, ",", "."))
        End If
   For x = 2 To 41
   Sheets("Sheet1").Cells(x, 1).Validation.Delete
   Sheets("Sheet1").Cells(x, 1).Validation.Add Type:=xlValidateList, Formula1:=Join(dizi.ToArray, ",")

 Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

        In order for these lists to be dynamic, that is if there is a change in the B column of the Products sheet where we receive the data; in order to reflect this change to data validation lists, we have added the following codes to the Worksheet_Change method of the Products sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then
Exit Sub
Call Benzersiz_Alfabetik_Liste
End If
End Sub

💡  Veri = Replace (Data, ",", ".") codes, if there is "," (comma) in the product values, it will "." replaces with dot. Because the comma character is used in the array and when Excel sees a comma, it acts with the assumption of a new list item.

        When there are changes in column B of the Products page, the macro that creates the data validation lists in Sheet1 will be triggered.
If desired ,Worksheet_Activate method for Sheet1 can be added to create these lists automatically when Sheet1 is active :
Private Sub Worksheet_Activate()
Call Benzersiz_Alfabetik_Liste
End Sub

No comments:

Post a Comment