Private Sub CommandButton1_Click() 'bouton Valider
Dim deb As Date, fin As Date, t1, t2, tablo, i&, n&
If Not IsDate(TextBox1) Then TextBox1 = "": TextBox1.SetFocus: Exit Sub
If Not IsDate(TextBox2) Then TextBox2 = "": TextBox2.SetFocus: Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'calcul manuel
deb = CDate(TextBox1)
fin = CDate(TextBox2)
[A3] = UCase("période du " & Format(deb, "d mmmm yyyy") & " au " & Format(fin, "d mmmm yyyy"))
[A3] = Replace([A3], " 1 ", " 1ER ")
Rows.Hidden = False 'RAZ
[A10:C72,H10:H72] = "" 'RAZ
t1 = [A10:C72]: t2 = [H10:H72]
tablo = Feuil1.[A1].CurrentRegion.Resize(, 27) 'matrice, plus rapide
For i = 2 To UBound(tablo)
If tablo(i, 2) >= deb And tablo(i, 2) <= fin And tablo(i, 12) = "Convention" Then
n = n + 1
If n < 64 Then 'sécurité
t1(n, 1) = tablo(i, 2): t1(n, 2) = tablo(i, 3)
t1(n, 3) = tablo(i, 27): t2(n, 1) = tablo(i, 10)
End If
End If
Next
[A10:C72] = t1: [H10:H72] = t2
[A10:H72].Sort [A10], xlAscending, Header:=xlNo 'tri
If n < 63 Then Rows(n + 10 & ":72").Hidden = True 'Total en ligne 73
Application.Calculation = xlCalculationAutomatic
Unload Me
Application.ScreenUpdating = True
If n > 63 Then MsgBox "Le nombre de lignes du tableau est insuffisant, réduisez la période..."
End Sub