HOWEVER, I'm thinking this should be the most efficient code...

Sub RemoveComments()
Dim R As Range
Const CommentColumn As String = "C"
Const FirstDeleteCommentRow As Long = 14
On Error GoTo NoComments
For Each R In Worksheets("Sheet8").Range( _
Cells(FirstDeleteCommentRow, CommentColumn), _
Cells(Rows.Count, CommentColumn)). _
SpecialCells(xlCellTypeComments)
R.Offset(, -1).Value = R.Comment.Text
R.Comment.Delete
Next
NoComments:
End Sub

as it only loops through the cells that actually have comments in them
within the column range of interest (if you have 2 cells with comments in
them in the desired column range, then the loop only iterates 2 times).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Of course, it would have helped if I had restricted the copying of the
comments from Column "C" only though (which is what I believe the OP
wanted)...

Sub RemoveComments()
Dim C As Comment
Const LastNonDeleteCommentRow As Long = 13
For Each C In Comments
If C.Parent.Column = 3 Then
If C.Parent.Row LastNonDeleteCommentRow Then
C.Parent.Offset(, -1).Value = C.Text
C.Delete
End If
End If
Next
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Just out of curiosity, did you see the code I posted back in your
original thread? I still think it would be more efficient.

Sub RemoveComments()
Dim C As Comment
Const LastNonDeleteCommentRow As Long = 13
For Each C In Comments
If C.Parent.Row LastNonDeleteCommentRow Then
C.Parent.Offset(, -1).Value = C.Text
C.Delete
End If
Next
End Sub

--
Rick (MVP - Excel)


"camlad" wrote in message
...
Thanks Mike, it was the "On Error Resume Next" I had forgotten.

Camlad


"Mike H" wrote in message
...
maybe this which continues on error

Sub RemoveComments()
Count = 13
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
While LastRow Count
On Error Resume Next
CommentText = Cells(Count, 3).Comment.Text
Cells(Count, 2) = CommentText
Cells(Count, 3).Comment.Delete
Count = Count + 1
CommentText = ""
Wend
End Sub

Mike

"camlad" wrote:

With recent help from Bob Alhat and Gary"s Student the macro below
works
until there is a cell without a comment. I need an "If" statement
which will
skip a "no comment" cell.

Please!

Camlad

Sub RemoveComments()
Count = 13
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
While LastRow Count
If Cells(Count, 3).........(what do I put here for "has a
comment").............Then
CommentText = Cells(Count, 3).Comment.Text
Cells(Count, 2) = CommentText
Cells(Count, 3).Comment.Delete
End If
Count = Count + 1
Wend
End Sub