Removing comments

  • Thread starter Thread starter camlad
  • Start date Start date
C

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
 
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
 
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
 
Back
Top