Sub Macro1()
Dim O As Worksheet
Dim PL As Range
Dim TV As Variant
Dim LIST As String
Dim TMP As Variant
Set O = Worksheets("Ma Compet 1")'à adapter
O.Unprotect "toto" 'mot de passe à adapter
Set PL = O.Range("D5").CurrentRegion
Set PL = PL.Offset(1, 0).Resize(PL.Rows.Count - 1, 1)
TV = PL
TMP = TV
LIST = Join(Application.Transpose(TMP), ";")
MsgBox LIST
O.Protect "toto" 'mot de passe à adapter
End Sub