Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - Clearing All Comments | Excel Discussion (Misc queries) | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
Clearing Comments? | Excel Programming | |||
Code seems to crash after clearing comments | Excel Programming | |||
Code crashes after clearing comments | Excel Worksheet Functions |