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
|