XL 2016 Pour les pros de la manipulation de données

Sub Worksheet_Activate()
    Dim T, L%, L2%, Ligne%
    Set F = Sheets("J'ai")
    Application.ScreenUpdating = False
    Range("A1:B" & Range("A65500").End(xlUp).Row).ClearContents
    T = F.[A1].CurrentRegion
    Ligne = 1
    For L = 1 To UBound(T)
        For L2 = 2 To UBound(T, 2)
            If T(L, L2) <> "" Then
                Cells(Ligne, 1) = T(L, 1)
                Cells(Ligne, 2) = T(L, L2)
                Ligne = Ligne + 1
            End If
        Next L2
    Next L
End Sub

Option Explicit

Sub Essai()
  If ActiveSheet.Name <> "j'ai" Then Exit Sub
  Dim dlg&: dlg = Cells(Rows.Count, 1).End(3).Row
  If dlg = 1 And IsEmpty([A1]) Then Exit Sub
  Dim ref$, chn$, lg1&, lg2&, col As Byte
  lg2 = 1: Application.ScreenUpdating = 0
  With Worksheets("je veux")
    For lg1 = 1 To dlg
      ref = Cells(lg1, 1)
      For col = 2 To 4
        chn = Cells(lg1, col)
        If chn <> "" Then
          .Cells(lg2, 1) = ref
          .Cells(lg2, 2) = chn
          lg2 = lg2 + 1
        End If
      Next col
    Next lg1
    .Select
  End With
End Sub

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(Source, {"Colonne1"}, "Attribut", "Valeur"),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Supprimer le tableau croisé dynamique des autres colonnes",{"Attribut"})
in
    #"Colonnes supprimées"

Option Explicit

Sub Essai()
  If ActiveSheet.Name <> "j'ai" Then Exit Sub
  Dim dlg&: dlg = Cells(Rows.Count, 1).End(3).Row
  If dlg = 1 And IsEmpty([A1]) Then Exit Sub
  Dim ref$, chn$, lg1&, lg2&, dh As Byte
  lg2 = 1: dh = 1: Application.ScreenUpdating = 0
  With Worksheets("je veux")
    For lg1 = 1 To dlg
      ref = Cells(lg1, 1): chn = Cells(lg1, 2)
      With .Cells(lg2, 1)
        If .Value <> "" Then _
          If ref <> .Value Then lg2 = lg2 + 1: dh = 1
      End With
      With .Cells(lg2, 1)
        .Value = ref: .Offset(, dh) = chn: dh = dh + 1
      End With
    Next lg1
    .Select
  End With
End Sub