Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, [L1]) Is Nothing Then Exit Sub
Dim deb1 As Range, deb2 As Range, d As Object, ncol%, tablo, i&, x$, j%
Cancel = True
Set deb1 = [B1]: Set deb2 = [G1]
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
'---liste des attendus---
With deb1.CurrentRegion.Offset(1)
ncol = .Columns.Count
If ncol = 1 Then ncol = 2
tablo = .Resize(, ncol)
End With
For i = 1 To UBound(tablo)
x = ""
For j = 1 To ncol
x = x & Chr(1) & tablo(i, j)
Next j
d(Mid(x, 2)) = ""
Next i
'---élimination des reçus---
tablo = deb2.CurrentRegion.Offset(1).Resize(, ncol)
For i = 1 To UBound(tablo)
x = ""
For j = 1 To ncol
x = x & Chr(1) & tablo(i, j)
Next j
x = Mid(x, 2)
If d.exists(x) Then d.Remove x
Next i
'---restitution des manquants---
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If FilterMode Then ShowAllData
With Target(2, 1)
.Resize(Rows.Count - .Row + 1, ncol).ClearContents 'RAZ
If d.Count = 0 Then Exit Sub
With .Resize(d.Count)
.Value = Application.Transpose(d.keys) 'Transpose est limitée à 65536 lignes
.TextToColumns .Cells(1), xlDelimited, Other:=True, OtherChar:=Chr(1) 'commande Convertir
End With
End With
End Sub
