View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default Deleting comments

You don't have to use "on error" in this case. You can be more direct.

Option Explicit
Sub RemoveComments()
Dim iRow As Long
Dim LastRow As Long
Dim CommentText As String

iRow = 13
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
While LastRow iRow
If .Cells(iRow, 3).Comment Is Nothing Then
'no comment
Else
CommentText = .Cells(iRow, 3).Comment.Text
.Cells(iRow, 2) = CommentText
.Cells(iRow, 3).Comment.Delete
End If
iRow = iRow + 1
Wend
End With
End Sub

If you have lots of cells but only a few cells with comments, you could just
look at the cells with comments this way:

Option Explicit
Sub RemoveComments2()
Dim myRng As Range
Dim myCell As Range

Dim LastRow As Long
Dim CommentText As String

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("C13:C" & LastRow) _
.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If myRng Is Nothing Then
'no comments in that range
Else
For Each myCell In myRng.Cells
CommentText = myCell.Comment.Text
.Cells(myCell.Row, 2) = CommentText
.Cells(myCell.Row, 3).Comment.Delete
Next myCell
End If
End With
End Sub

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


--

Dave Peterson