Sub Test()
Dim zoneA As Excel.Range, zoneB As Excel.Range, curCell As Excel.Range, iL As Long
With ThisWorkbook.Sheets("bdd A")
Set zoneA = .Range("K2:K" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With
With ThisWorkbook.Sheets("bdd B")
Set zoneB = .Range("K2:K" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With
zoneA.FormulaR1C1 = "=RC1&""|""&RC2&""|""&RC3&""|""&RC4&""|""&RC5&""|""&RC6&""|""&RC7&""|""&RC8&""|""&RC9&""|""&RC10"
zoneA.Value = zoneA.Value
zoneB.FormulaR1C1 = "=RC1&""|""&RC2&""|""&RC3&""|""&RC4&""|""&RC5&""|""&RC6&""|""&RC7&""|""&RC8&""|""&RC9&""|""&RC10"
zoneB.Value = zoneB.Value
iL = 1
With ThisWorkbook.Sheets("bdd C")
For Each curCell In zoneB
If zoneA.Find(curCell.Text, , xlValues, xlWhole) Is Nothing Then
iL = iL + 1
.Range("A" & iL) = curCell.Text
End If
Next curCell
If iL <> 1 Then
.Range("A2:A" & iL).TextToColumns Destination:=.Range("A2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), _
TrailingMinusNumbers:=True
End If
End With
zoneA.Clear
zoneB.Clear
End Sub