Removing comments
 

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

Removing comments
 

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

"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).........(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

Removing comments
 

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

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
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

"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).........(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