Replace the below part in the same macro suggested by Jim////

If Not rngComments Is Nothing Then
For Each rng In rngComments
MsgBox rng.Address & vbCrLf & rng.Comment.Text
Next rng
End If

with..

Dim lngRow as Long
If Not rngComments Is Nothing Then
For Each rng In rngComments
lngRow = lngRow + 1
Sheets("Comments").Range("A" & lngRow) = rng.Comment.Text
Next rng
End If

This will extract all comments to Col A of a sheet named 'Comments'. Create
a sheet named 'Comments' and try

If this post helps click Yes
---------------
Jacob Skaria


"dwake" wrote:

Is there a way to pull it out and put it in a blank cell as opposed to a
message box?

"Jim Thomlinson" wrote:

A macro will do it...

Public Sub WhatEver()
Dim rngComments As Range
Dim rng As Range

On Error Resume Next
Set rngComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If Not rngComments Is Nothing Then
For Each rng In rngComments
MsgBox rng.Address & vbCrLf & rng.Comment.Text
Next rng
End If
End Sub
--
HTH...

Jim Thomlinson


"dwake" wrote:

okay, so I am looking at a spreadsheet (not created by me) and I need to pull
out some information in the spreadhseet. The problem is they put all the
information in comment field instead of putting them in rows. the
spreadsheet is built with each column being 1 month, and when there is an
action done in that month, they put all the information in the comments
field. The comments field is standardized. Can I create something to go in
and pull all the information out of each comments field that is spread across
4 years?