Delete comments in the workbook except few cells: CODE given

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thulasiram - 15 Jan 2007 23:42 GMT

Hello all,

Code given below deletes the comments in all the cells in the
worksheets.

How to modify the code such that comments in the cell A2 and B1 of EACH
sheet is not deleted but delete all the other comments in the
worksheet?

Dim sh As Worksheet
Dim cmt As Comment

For Each sh In ThisWorkbook.Worksheets
For Each cmt In sh.Comments
cmt.Delete
Next
Next

Thanks,
Thulasiram

Reply to this Message

Dave Peterson - 16 Jan 2007 00:16 GMT

Dim sh As Worksheet
Dim cmt As Comment

For Each sh In ThisWorkbook.Worksheets
For Each cmt In sh.Comments
select case ucase(cmt.parent.address(0,0))
case is = "A2", "B1"
'do nothing
case else
cmt.Delete
end select
Next cmt
Next sh

(Watch for typos!)

> Hello all,
>
[quoted text clipped - 16 lines]
> Thanks,
> Thulasiram

Signature

Dave Peterson

Reply to this Message

Thulasiram - 16 Jan 2007 01:13 GMT

Dear Dave,

Code works great! Thanks a ton!

> Dim sh As Worksheet
> Dim cmt As Comment
[quoted text clipped - 32 lines]
> > Thanks,
> > Thulasiram

Reply to this Message

Thulasiram - 16 Jan 2007 04:41 GMT

In the previous case, the cells were fixed.. i.e. cells A2 and B1. What
would be the code to retain comment in A2:sheet1, V1 in sheet3, C4 in
sheet5 and so on..

If I have case is = "A2", "V1", "C4" then comments in cells A2, V1 and
C4 in all sheet are retained.. I dont want that.. How to make the
condition dynamic?

Please help!

Thanks,
Thulasiram

> Dear Dave,
>
[quoted text clipped - 36 lines]
> > > Thanks,
> > > Thulasiram

Reply to this Message

Thulasiram - 16 Jan 2007 07:50 GMT

Someone please help me in this issue. I need your expert help asap! I
am refreshing the thread every few minutes..

Thanks...

> In the previous case, the cells were fixed.. i.e. cells A2 and B1. What
> would be the code to retain comment in A2:sheet1, V1 in sheet3, C4 in
[quoted text clipped - 49 lines]
> > > > Thanks,
> > > > Thulasiram

Reply to this Message

Dave Peterson - 16 Jan 2007 15:29 GMT

What does "and so on" mean?

Is there a pattern?

If not, you could just add some more checks.

Dim sh As Worksheet
Dim cmt As Comment

For Each sh In ThisWorkbook.Worksheets

select case ucase(sh.name)
case is = "SHEET1", "SHEET99"
For Each cmt In sh.Comments
select case ucase(cmt.parent.address(0,0))
case is = "A2", "B1"
'do nothing
case else
cmt.Delete
end select
Next cmt

case is = "SHEET 33", "SHEET 333"
For Each cmt In sh.Comments
select case ucase(cmt.parent.address(0,0))
case is = "X2", "V1"
'do nothing
case else
cmt.Delete
end select
Next cmt

End Select
Next sh

> In the previous case, the cells were fixed.. i.e. cells A2 and B1. What
> would be the code to retain comment in A2:sheet1, V1 in sheet3, C4 in
[quoted text clipped - 53 lines]
> > >
> > > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Thulasiram - 16 Jan 2007 18:30 GMT

Dear Dave,

So on.. did not mean any pattern.. I actualy wanted the code that would
include both sheetname and cell's address. You solved my problem..

Thanks..

> What does "and so on" mean?
>
[quoted text clipped - 88 lines]
> > > >
> > > > Dave Peterson

Reply to this Message