Private Sub Worksheet_Change(ByVal Target As Range)
Dim Filter As String, Col As Long, lastRow As Long
If Not Intersect(Target, ActiveSheet.Range("B5")) Is Nothing Then
Application.ScreenUpdating = False
If Me.FilterMode Then Me.ShowAllData
Filter = ActiveSheet.Range("B5")
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
If Filter <> "" Then
If IsNumeric(Filter) Then
Col = 1
Filter = CStr(Filter)
Else
Col = 2
End If
With Range("A9:D" & CStr(lastRow)).Select
ActiveSheet.Range("$A$8:$D$" & CStr(lastRow)).AutoFilter Field:=Col, Criteria1:="*" & Filter & "*"
End With
End If
MsgBox "Nb:" & ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
End If
Application.ScreenUpdating = True
End Sub