They say 3rd time's the charm...
...they lied.
Well below is the code in the document. Why it crashes, I don't know...
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Me.ListObjects(1).Range.AutoFilter Field:=2, Criteria1:=Target.Value
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rg As Range, ar As Variant
Set rg = Me.Cells(6, 1).CurrentRegion.Columns(2)
Set rg = rg.Offset(1).Resize(rg.Rows.Count - 1)
ar = Data_Validation_List(rg)
With Me.Cells(3, 2).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(ar, ",")
End With
If Target.Address = "$F$3" Then
Me.Cells(3, 2).ClearContents
Me.ListObjects(1).Range.AutoFilter Field:=2, Criteria1:="*"
Me.Cells(3, 2).Select
End If
End Sub
Function Data_Validation_List(rg As Range) As Variant
Dim e, ar
ar = Application.Index(rg.Value, 0, 0)
With CreateObject("Scripting.Dictionary")
For Each e In ar
.Item(e) = Empty
Next
Data_Validation_List = .keys
End With
End Function
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks