Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rws As Long, Rng As Range, c As Range, s As String
Dim rw1 As Long, r2 As Range, j19 As Range
Dim rw2 As Long, r3 As Range, L19 As Range, c1 As Range, rng2 As Range
Rws = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(2, 1), Cells(Rws, 1))
Set rng2 = Range(Cells(2, 2), Cells(Rws, 2))
Set j19 = Range("J19")
Set L19 = Range("L19")
If Target.Count > 1 Then Exit Sub
If Target.Address = "$H$19" Then
s = Target.Value
Range("P4:P1000").ClearContents
Range("R4:R1000").ClearContents
'--------Country-------
For Each c In Rng.Cells
If c = s Then
y = Application.WorksheetFunction.CountIf(Range(Cells(1, 2), Cells(c.Row, 2)), c.Offset(, 1))
If y = 1 Then Cells(Rows.Count, "P").End(xlUp).Offset(1, 0) = c.Offset(0, 1)
End If
Next c
rw1 = Cells(Rows.Count, "P").End(xlUp).Row
Set r2 = Range(Cells(4, "P"), Cells(rw1, "P"))
r2.Name = "cty"
With j19.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=cty"
End With
End If
'--------Centers-------
If Target.Address = "$J$19" Then
s = Target.Value
For Each c1 In rng2.Cells
If c1 = s Then
x = Application.WorksheetFunction.CountIf(Range(Cells(1, 3), Cells(c1.Row, 3)), c1.Offset(, 1))
If x = 1 Then Cells(Rows.Count, "R").End(xlUp).Offset(1, 0) = c1.Offset(0, 1)
End If
Next c1
rw2 = Cells(Rows.Count, "R").End(xlUp).Row
Set r3 = Range(Cells(4, "R"), Cells(rw2, "R"))
r3.Name = "ctr"
With L19.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=ctr"
End With
End If
End Sub
Bookmarks