Hi,

First change your security level-Tools>Options>Security>Macro
Security>Choose the “Low” option.

Then Copy this code:

Sub showcomments()
'posted by Dave Peterson 2003-05-16
   Application.ScreenUpdating = False

   Dim commrange As Range
   Dim cmt As Comment
   Dim curwks As Worksheet
   Dim newwks As Worksheet
   Dim i As Long

   Set curwks = ActiveSheet

   On Error Resume Next
   Set commrange = curwks.Cells _
       .SpecialCells(xlCellTypeComments)
   On Error GoTo 0

   If commrange Is Nothing Then
      MsgBox "no comments found"
      Exit Sub
   End If

   Set newwks = Worksheets.Add

    newwks.Range("A1:D1").Value = _
        Array("Number", "Name", "Value", "Comment")

   i = 1
   For Each cmt In curwks.Comments
      With newwks
        i = i + 1
        On Error Resume Next
        .Cells(i, 1).Value = i - 1
        .Cells(i, 2).Value = cmt.Parent.Name.Name
        .Cells(i, 3).Value = cmt.Parent.Value
        .Cells(i, 4).Value = cmt.Parent.Address
        .Cells(i, 5).Value = Replace(cmt.Text, Chr(10), " ")
      End With
   Next cmt

   newwks.Cells.WrapText = False
   newwks.Columns.AutoFit

   Application.ScreenUpdating = True

End Sub

Go to your workbook>Alt+F11>Insert>Module and Paste the copied Code.
Close the VBA Editor>Select your worksheet with the comments>Alt+F8> and
Run the macro “showcomments”.
After running the macro it will list all comments from your worksheet
regardless in which column are they on a new Worksheet. If you have comments
in more the one column and you insist to list only the comments from that
particular column then use the Mike’s suggestion.
Hope this helps.

Tim