Option Explicit
Const CelluleDestination = "d5"
Sub AlimentsSansDoublon()
Dim derCellule As Range, source As Range
With Sheets("Feuil6")
Set source = .Range(.Range("a1"), DerCellNonVide(.Range("a:a"))): End With
With Sheets("Feuil1").Range(CelluleDestination)
.EntireColumn.Clear
source.Copy .Range("a1")
.Resize(source.Rows.Count).RemoveDuplicates Columns:=1, Header:=xlYes
.Value = "Extraction"
.EntireColumn.AutoFit
End With
End Sub
Function DerCellNonVide(xplage As Range) As Range
Dim i&, j&
On Error Resume Next
i = Application.Match(999 ^ 9, xplage.Columns(1), 1)
j = Application.Match(String(255, "z"), xplage.Columns(1), 1)
On Error GoTo 0
If i + j <> 0 Then Set DerCellNonVide = xplage.Columns(1).Cells(IIf(i > j, i, j), 1)
End Function