Sub ListeCommentaires()
Dim a(), c As Comment, n&
With Feuil1 'CodeName
ReDim a(1 To .Comments.Count + 1, 1 To 3) 'tableau, plus rapide
a(1, 1) = "Adresse": a(1, 2) = "Texte"
n = 1
For Each c In .Comments
n = n + 1
a(n, 1) = c.Parent.Address(0, 0)
a(n, 2) = c.Text
a(n, 3) = c.Parent.Row
Next
End With
'---restitution---
Application.ScreenUpdating = False
With Feuil2 'CodeName
If .FilterMode Then .ShowAllData 'si la feuille est filtrée
With .[A1].Resize(n, 3)
.Value = a
.Sort .Columns(3), xlAscending, Header:=xlYes 'tri sur la 3ème colonne
.Columns(3).Delete xlToLeft
.Offset(n).Resize(.Parent.Rows.Count - n).Delete xlUp 'RAZ dessous
End With
.Activate 'facultatif
End With
End Sub