Private Sub Worksheet_Activate()
Dim col%, ncol%, tablo, resu(), i&, n&, j%
col = 23 'colonne W
With Sheets("Feuil2").[A1].CurrentRegion
ncol = IIf(.Columns.Count < col, col, .Columns.Count)
tablo = .Resize(, ncol) 'matrice, plus rapide
ReDim resu(1 To UBound(tablo) + Application.CountA(.Columns(col).Offset(1)), 1 To ncol - 1)
End With
For i = 1 To UBound(tablo)
n = n + 1
For j = 1 To ncol
resu(n, j + (j > col)) = tablo(i, j) 'décalage APRES W (True => -1)
Next j
If n = 1 Then resu(n, col - 1) = "ADRESSE"
If tablo(i, col) <> "" And n > 1 Then
n = n + 1
For j = 1 To ncol
resu(n, j + (j >= col)) = tablo(i, j) 'décalage A PARTIR de W
Next j
End If
Next i
'---restitution---
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A1] '1ère cellule de restitution
.Resize(n, ncol - 1) = resu
.Offset(n).Resize(Rows.Count - n - .Row + 1, ncol - 1).ClearContents 'RAZ en dessous
End With
Columns.AutoFit 'ajustement largeurs
Cells.HorizontalAlignment = xlCenter 'centrage
End Sub