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

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 wrote:

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

Dear Dave,

Code works great! Thanks a ton!


Dave Peterson wrote:
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 wrote:

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

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 wrote:
Dear Dave,

Code works great! Thanks a ton!


Dave Peterson wrote:
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 wrote:

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

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

Thanks...

Thulasiram wrote:
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 wrote:
Dear Dave,

Code works great! Thanks a ton!


Dave Peterson wrote:
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 wrote:

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

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 wrote:

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 wrote:
Dear Dave,

Code works great! Thanks a ton!


Dave Peterson wrote:
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 wrote:

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

--

Dave Peterson

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


Dave Peterson wrote:
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 wrote:

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 wrote:
Dear Dave,

Code works great! Thanks a ton!


Dave Peterson wrote:
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 wrote:

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

--

Dave Peterson