Clearing Comments
 

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

Clearing Comments
 

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





"kirkm" wrote:


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

Clearing Comments
 

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

--
Rick (MVP - Excel)


"kirkm" wrote in message ...

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

Clearing Comments
 

On Sat, 21 Feb 2009 19:18:01 -0800, JLGWhiz
wrote:

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

Clearing Comments
 

On Sat, 21 Feb 2009 22:18:56 -0500, "Rick Rothstein"
wrote:

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

Clearing Comments
 

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

"kirkm" wrote:

On Sat, 21 Feb 2009 19:18:01 -0800, JLGWhiz
wrote:

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