Drop down list for the criteria range of the filter function

[MrRobinson]

hello all,

I have created a VBA code which will automatically filter the "database" sheet and paste the filtered data on another sheet and finally sort ascending the last column of the filtered data. This is the code :

Private Sub SpinButton1_Change()
ActiveWindow.DisplayVerticalScrollBar = True


End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A4:F4")) Is Nothing Then
Dim DataSH As Worksheet
Set DataSH = Sheets("Database")
DataSH.Range("A:M").AdvancedFilter action:=xlFilterCopy, copytorange:=Range("A9:M9"), criteriarange:=Range("A3:F4")
Range("A10:J500").Sort Key1:=Range("J10"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlStroke, DataOption1:=xlSortNormal
End If
End Sub

However i want to create a drop down list on each column of the criteria range. Therefore i can choose the options in the lists instead of typing them.

I have try to create the combo box but it will list out all the options and repeat the same options. How can i make the list to only list out the options without repeating? Because the database will be updated from time to time, therefore i have to make it automatically remove the duplicated options in the drop down list instead of manually remove it.



Can anyone help me out with this? Thanks