Option Base 1

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Dim Rng As Range
Set Rng = Range("F2")

'=if chage happend in defined range then event triggers=====
If Not Intersect(Target, Range("F2")) Is Nothing Then

'Clear the existing content
Range("F5:F" & Range("F5").End(xlDown).Row).ClearContents

'Assign the Column Number based on Data exists in Drop down
Dim ColNumb As Integer

If Range("F2").Value = "Item" Then
ColNumb = 1

ElseIf Range("F2").Value = "Price" Then
ColNumb = 2

ElseIf Range("F2").Value = "Zone" Then
ColNumb = 3
End If

'Find the Last Row
Dim LastRow As Integer
LastRow = Cells(Rows.Count, ColNumb).End(xlUp).Row

'Declare Array variable
Dim Arry() As Variant

'insert values into array
ReDim Arry(LastRow - 4)
Arry = Range(Cells(5, ColNumb), Cells(LastRow, ColNumb))


Dim UniqueArry() As Variant
ArrayNumb = 0
'External Loop -- to loop through all the elements of the array
For r = 1 To UBound(Arry)
DataFound = ""
Datas = Arry(r, 1)

'Internal Loop -- To Loop upto current element -1 to find dupes
For Rs = 1 To r
If Datas = Arry(Rs, 1) And r <> 1 And Rs < r Then
DataFound = "Yes"
Exit For
End If
Next

'If data doesn't hit then insert values into new array
If DataFound <> "Yes" Then
ArrayNumb = ArrayNumb + 1
ReDim Preserve UniqueArry(ArrayNumb)
UniqueArry(ArrayNumb) = Datas
End If
Next

'Extract data from the Loop
For U = 1 To UBound(UniqueArry)
Cells(4 + U, 6).Value = UniqueArry(U)
Next

End If
'======Nullify the Array variables=======
Erase Arry
Erase UniqueArry

Application.EnableEvents = True
End Sub