Delete all comments which are blank or contains User Name

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
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.


Code:
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
 
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.
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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:

Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top