Delete all comments which are blank or contains User Name

[leonlai]

Hi,

I am writing a macro that will delete all comments (on the worksheet) that are empty.
By "Empty" , I mean either of 2 things:

(1) The comment box is absolutely empty: nothing in it.
The user can create such a comment by selecting a cell and clicking a macro button.
The macro creates a comment without User Name.
I created the macro to save space because there may be many comments on the sheet.

(2) But if the user creates a comment using Excel's menu (Insert Comment), and forgets typing anything else, I also
consider it to be an empty comment.

The comment will contain (in bold), say MYCOMPANY:
But if the comment contains something else, say MYCOMPANY: abcdefgh, it is not empty.I tried the code below, but it deletes only those comments that are completely empty.
It does not delete those comments that contain ONLY the User Name.

It does not work even if I hard-code the User Name.

Sub DeleteEmptyComments()
  Dim C As Comment
  Dim User As String
   
  User = Application.UserName & ":"
  
For Each C In Worksheets("Sheet1").Comments
    If C.Text = User Or C.Text = "" Then C.Delete
    
  Next
End Sub

How can we modify the Sub to make it do what I want?

Thanks
Leon

 

[igold]

It's the extra line feed character that is automatically inserted by Excel Chr(10). It is why when you enter a new comment the text always starts on the a new line below the user name.

 

[leonlai]

Hi, igold

Thanks for your reply.

It is only now that I am having some time to try your code.

If all my empty comments were created using Insert Comment, then your code works perfectly, and all are deleted. These all contain the User Name in bold ending with ":"

But if there are comments which are completely empty (created by clicking on a macro button), then the program crashes at this line:

If C.Text = User Or C.Text = "" Or Asc(Mid(C.Text, Len(C.Text), 1)) = 10 Then C.Delete

I don't know where is the problem.

Best Regards,
Leon

 

[leonlai]

Hi, igold

You are right.

That's the problem we are all trying to solve.

Thanks

 

[igold]

I would like to see the line which auto creates a comment.

Just use the logic that is working for you and remember that this character is always going to be the first character to the right (which is going to be a new line) of the user name and ":". Trim is not going make this character go away. You are going to have actively deal with it.

 

[leonlai]

Hi, daverunt

Thanks a lot for your reply.

It is only now that I can try your code.
It works perfectly, and deletes all comments which are completely empty and also those which bear the User Name only.

It is going to be very useful in my project, because there may be dozens of comments on our worksheets, and empty comments which have been created accidentally must be cleared.

Best Regards,

Leon

closing

 

[daverunt]