Clearing Comments

[kirkm]

I'm using this code in a function and sending in the
line number as 'p' -

--
With Range("O" & Val(p))
If Not .Comment Is Nothing Then
With .Comment
.Visible = False
End With
End If
End With
--

It is feasible to use the highest line number insted of p
avoiding the need to calcualte p - or might that be
significantly slower?

Perhaps there's a better method to globally hide
any/all comments ?


Thanks - Kirk

 

[JLGWhiz]

To clear contents and comments:
Sub dk()
ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeComments).Delete
End Sub

To clear comments only:

Sub sl()
Dim c As Comment
For Each c In ActiveSheet.Comments
If Not c Is Nothing Then
c.Delete
End If
Next
End Sub

 

[Rick Rothstein]

The help files for the Comments Collection Object (found by pressing F1 on
the word "Comments") offers this code example...

Use the Comments property to return the Comments collection. The following
example hides all the comments on worksheet one.

Set cmt = Worksheets(1).Comments
For Each c In cmt
c.Visible = False
Next

 

[kirkm]

To clear contents and comments:
Sub dk()
ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeComments).Delete
End Sub

To clear comments only:

Sub sl()
Dim c As Comment
For Each c In ActiveSheet.Comments
If Not c Is Nothing Then
c.Delete
End If
Next
End Sub

I found that both routines c.Delete removes the comment
completely.

Cheers - Kirk

 

[kirkm]

The following
example hides all the comments on worksheet one.

Set cmt = Worksheets(1).Comments
For Each c In cmt
c.Visible = False
Next

Yes, would there be any speed increase
tospecify a column rather thann the whole sheet?

Assuming it's possible ?

Thanks - Kirk

 

[JLGWhiz]

Yes, both will remove all comments, but the first one also removes any other
values in the cell and then shifts cells upward in those columns. So, if you
only want to remove comments, the second one is probably what you should use.
You can specify any range of cells. Example:

For Each c In ActiveSheet.Range("A2:G40").Comments
'code to delete
Next