Delete comments in the workbook except few cells: CODE given

[Thulasiram]

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

 

[Dave Peterson]

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!)

 

[Thulasiram]

Dear Dave,

Code works great! Thanks a ton!

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!)

 

[Thulasiram]

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

 

[Thulasiram]

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

Thanks...

 

[Dave Peterson]

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

 

[Thulasiram]

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..