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

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
>

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 news:(E-Mail Removed)...
>
> 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

On Sat, 21 Feb 2009 19:18:01 -0800, JLGWhiz
<(E-Mail Removed)> 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

On Sat, 21 Feb 2009 22:18:56 -0500, "Rick Rothstein"
<(E-Mail Removed)> 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

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
> <(E-Mail Removed)> 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
>