VBA to Delete Empty Comments

[azsxdc]

Hi,

I am a VBA Newbie. I was wondering whether it is possible to get a Macro to search for empty comments (or comments with specific values) and Delete them?

Thank you.

 

[Rick Rothstein]

You were not clear whether you wanted to only delete empty comments on a particular sheet or on every worksheet in the workbook, so I assumed the latter. Give this macro a try...

Sub DeleteComments()
  Dim C As Comment, WS As Worksheet
  Const TextToFind As String = ""
  For Each WS In Worksheets
    For Each C In WS.Comments
      If UCase(C.Text) = UCase(TextToFind) Then C.Delete
    Next
  Next
End Sub

If you want to delete comments with a particular text instead of empty comments, just put that text between the two quote marks in the statement that begins with the Const keyword.

 

[azsxdc]

Thank you for your answer Rick.

Only on sheet has comments. However, the Workbook is big so it is best to limit the search to one sheet.

I have tried tour Macro, putting it in a module but when I click on the Macro button your Macro is not listed.

 

[azsxdc]

I have managed to trigger the Macro but the empty comments are not deleted.

 

[azsxdc]

Success ! I will try it on my big workbook page now.

 

[azsxdc]

Works fine. Thanks a lot Rick.

 

[Rick Rothstein]

Only one sheet has comments. However, the Workbook is big so it is best to limit the search to one sheet.

Here is the code to restrict the macro to only one sheet (assumed to be "Sheet1" for the code... change it as appropriate for your actual setup)...

Sub DeleteComments()
  Dim C As Comment
  Const TextToFind As String = ""
  For Each C In Worksheets("Sheet1").Comments
    If C.Text = TextToFind Then C.Delete
  Next
End Sub