[ RESOLU ] Classer par ordre alphabetique!!!!!

Function TriAlpha(r1, r2)
Dim a, b, vide&, i&, c()
a = r2: b = r1
vide = 2
For i = 1 To UBound(a)
  If a(i, 1) = "" Then
    vide = vide + 1
    a(i, 1) = String(vide, "z")
  End If
Next
tri a, b, 1, UBound(a)
ReDim c(1 To UBound(a), 1 To 2)
For i = 1 To UBound(a)
  If a(i, 1) Like "zzz*" Then a(i, 1) = ""
  c(i, 1) = b(i, 1)
  c(i, 2) = a(i, 1)
Next
TriAlpha = c 'matrice
End Function

Sub tri(a, b, gauc, droi)     ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2, 1)
g = gauc: d = droi
Do
    Do While a(g, 1) < ref: g = g + 1: Loop
    Do While ref < a(d, 1): d = d - 1: Loop
    If g <= d Then
      temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
      temp = b(g, 1): b(g, 1) = b(d, 1): b(d, 1) = temp
      g = g + 1: d = d - 1
    End If
Loop While g <= d
If g < droi Then Call tri(a, b, g, droi)
If gauc < d Then Call tri(a, b, gauc, d)
End Sub

=W3<>""

Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range
On Error Resume Next
Set P = [A:A].SpecialCells(xlCellTypeConstants, 1).EntireRow
On Error GoTo 0
If P Is Nothing Then Exit Sub
For Each P In P.Areas
  If Not Intersect(Target, P) Is Nothing Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Union(P(0, 2), P(0, 8), P.Columns(2), P.Columns(8)).Copy P(0, 22) 'avec titres
    P.Sort P.Columns(8), xlAscending, Header:=xlNo
    P.Resize(, 21).Sort P.Columns(1), xlAscending, Header:=xlNo
    Application.EnableEvents = True
  End If
Next
End Sub

Sub Classer()
Dim i&, P As Range, j As Byte
Application.ScreenUpdating = False
For i = 1 To ActiveSheet.UsedRange.Rows.Count
  If Cells(i, 2) = "N°" Then
    Set P = Rows(i + 1).Resize(20).Cells
    Union(P(0, 2), P(0, 8), P.Columns(2), P.Columns(8)).Copy P(0, 22) 'avec titres
    P.Columns(22).Resize(, 2).Sort P.Columns(23), xlAscending, Header:=xlNo
    P(1, 36).Resize(2, 3) = "" 'RAZ
    For j = 1 To 2 'les 2 premiers
      If P(j, 23) <> "" Then
        P(j, 36) = P(j, 22)
        P(j, 37) = P(j, 23)
        P(j, 38) = Application.VLookup(P(j, 36), P.Columns(2).Resize(, 3), 3, 0)
      End If
    Next j
  End If
Next i
End Sub