Delete comments in the workbook except few cells: CODE given

T

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
 
D

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

Thulasiram

Dear Dave,

Code works great! Thanks a ton!


Dave said:
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!)
 
T

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
 
T

Thulasiram

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

Thanks...
 
D

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
 
T

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top