Synthese des maladies

Option Compare Text 'la casse est ignorée

Private Sub Worksheet_Activate()
Dim t, rest(), ncol%, i&, cpt%, j%, n&
With Feuil2 'CodeName de la feuille des congés
  t = .Range("A1", .UsedRange) 'matrice, plus rapide
End With
ReDim rest(1 To UBound(t) - 4, 1 To 3)
ncol = UBound(t, 2)
'---remplissage du tableau---
For i = 5 To UBound(t)
  cpt = 0
  For j = 22 To ncol
    If t(i, j) = "MALADIE" Then cpt = cpt + 1
  Next
  If cpt Then
    n = n + 1
    rest(n, 1) = t(i, 2)
    rest(n, 2) = t(i, 3)
    rest(n, 3) = cpt
  End If
Next
'---restitution---
If n Then
  Application.ScreenUpdating = False
  [A3].Resize(n, 3) = rest
  Rows(n + 3 & ":" & Rows.Count).Delete
  [A3].Resize(n, 3).Sort [C3], xlDescending, Header:=xlNo 'tri décroissant
End If
End Sub