1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
| Option Explicit
Private Sub Worksheet_Activate()
Dim tablo1, nlig&, tablo2, tablo3, ncol%, j%, i&, n&
With Feuil1
tablo1 = .[A1].CurrentRegion.Resize(, 3)
nlig = UBound(tablo1)
If nlig = 1 Then GoTo 1
tablo2 = .[E1].CurrentRegion.Resize(nlig)
ncol = UBound(tablo2, 2)
End With
'---tableau des résultats---
ReDim resu(1 To ncol * (nlig - 1), 1 To 6)
For j = 1 To ncol
For i = 2 To nlig
If tablo2(i, j) <> "" Then
n = n + 1
resu(n, 1) = tablo2(1, j)
resu(n, 2) = tablo1(i, 1)
resu(n, 3) = tablo1(i, 2)
resu(n, 4) = tablo1(i, 3)
resu(n, 5) = tablo2(i, j)
End If
Next i, j
'---restitution---
1 If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] 'cellule de restitution, à adapter
If n Then
.Resize(n, 5) = resu
.Resize(n, 5).Borders.Weight = xlThin 'bordures
End If
.Offset(n).Resize(Rows.Count - n - .Row + 1, 5).Delete xlUp 'RAZ en dessous
End With
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub |
Partager