Private Sub Worksheet_Activate()
Dim lig&, col%, tablo1, tablo2(), i&
[Ref].Resize(, 2).Sort [Ref], Header:=xlYes 'tri
lig = Evaluate("SUMPRODUCT(N(Ref<>OFFSET(Ref,1,)))")
col = Evaluate("MAX(COUNTIF(Ref,Ref))") + 1
tablo1 = [Ref].Resize(, 2) 'matrice, plus rapide
ReDim tablo2(1 To lig, 1 To col)
lig = 1
For i = 2 To UBound(tablo1)
If tablo1(i, 1) <> tablo1(i - 1, 1) Then
lig = lig + 1
col = 1
tablo2(lig, 1) = tablo1(i, 1)
End If
col = col + 1
tablo2(lig, col) = tablo1(i, 2)
tablo2(1, col) = col - 1
Next
tablo2(1, 1) = "Référence"
Cells.Resize(lig, col) = tablo2
Rows(lig + 1 & ":" & Rows.Count).ClearContents
Range(Columns(col + 1), Columns(Columns.Count)).ClearContents
End Sub