j'ai réussi à enlever le message d'erreur mais toujours problème de mémoire.
J'ai testé en module standard ou de la feuille.
Voici la macro que j'ai modifié :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim aa, i&, a&, bb, d As Object, n&, cc, y&
Application.EnableEvents = False
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")
With Feuil1
aa = .Range("A3
" & .Range("A" & Rows.Count).End(3).Row)
End With
For i = 1 To UBound(aa)
If aa(i, 1) <> "" And Not d.exists(aa(i, 1)) Then d.Add aa(i, 1), aa(i, 1)
Next i
bb = d.keys(): n = 1
d.RemoveAll
ReDim cc(1 To UBound(bb) + 1, 1 To UBound(aa))
For i = 0 To UBound(bb)
cc(i + 1, 1) = bb(i)
For a = 1 To UBound(aa)
If aa(a, 1) = bb(i) Then
If Not d.exists(aa(a, 2)) Then
n = n + 1
d.Add aa(a, 2), aa(a, 2)
cc(i + 1, n) = aa(a, 2)
End If
End If
Next a
n = 1: d.RemoveAll
Next i
Sheets("commande").Range("V5:AC10000").ClearContents
Sheets("commande").Range("v5").Resize(UBound(cc), UBound(cc, 2)) = cc
Application.EnableEvents = True
End Sub
et voilà ce que ça m'affiche :