Removing comments

[camlad]

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).........(has not 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

 

[Mike H]

Hi,

A couple of points. You don't need an IF statement just an On error.
secondly I'm surprised it worked well becuase having populated the
commenttext variable becuase it wasn't being cleared if the next row didn't
have a comment then it used the old text. Try this

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

 

[Rick Rothstein]

I'm thinking this code will 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