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

 

[Yongle]

Try this - my Excel adds a colon after the username so this works for me
And it makes no difference who the user is

  If InStr(c.Text, ":") = Len(c.Text) Or c.Text = "" Then c.Delete

Compares position of first colon to length of comment text

 

[Yongle]

OR

If above does not help and getting the user name is required (I have added the colon because I need it)

you could try comparing comment text with one of these:

msgbox Application.UserName & ":"
MsgBox ActiveWorkbook.BuiltinDocumentProperties("Author").Value & ":"

But there may be more than one user adding comments - so may not be overly useful!

 

[igold]

How about this. There is an extra character automatically added if the the user just clicks "Insert Comment" and leaves the cell comment blank.

Sub DeleteEmptyComments()
    Dim C As Comment
    Dim User As String
   
    User = Application.UserName & ":"
  
    For Each C In Worksheets("Sheet01").Comments
        If C.Text = User Or C.Text = "" Or Asc(Mid(C.Text, Len(C.Text), 1)) = 10 Then C.Delete
    Next
End Sub

 

[daverunt]

The LF is added as igold states in a blank comment but they may also be found in badly edited comments

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

 

[igold]

@dave

I agree, but it would have to be a pretty bad hack job, I don't even know that there is a single keyboard stroke that adds a LF. That coupled with the fact that it would have to appear at the very end of the comment...

 

[leonlai]

Hi, Yongle

Your code is logically correct, but there is a problem with Excel (not your code).

My User Name is:
AAA BBB ABCDEFGHIJKL
---
3+1+3 +1+ 12 = 20

If we add the colon ":" in the Comment, this gives a length of 21 (including the blanks).

I added this MsgBox to find out the relevant values:
MsgBox ("Position of colon = " & InStr(C.Text, ":") & " Length of text = " & Len(C.Text))

If I create Comments using Insert Comments, this is what the MsgBox gives:

Position of colon = 21 (correct)
Length of Text = 22 (incorrect: it's always 1 more than the correct value)

If I incorporate this correction in your formula, it works fine!
But it's not reliable, because if somebody types blanks in the comments, the formula fails.

I prefer your next formula, which works foe me.

Leon

 

[leonlai]

Hi, Yongle

This works for me!

Best Regards,
Leon

 

[Yongle]

How about... to deal with extra spaces to right

 If InStr(RTrim(c.Text), ":") = Len(RTrim(c.Text)) Or c.Text = "" Then c.Delete

 

[leonlai]

Yongle,

How about... to deal with extra spaces to right
If InStr(RTrim(c.Text), ":") = Len(RTrim(c.Text)) Or c.Text = "" Then c.Delete

That's a tricky thing that's giving me headache.

Logically, it is correct. In practice, it does not give the correct answer.

I put the following in my code to see what happens (after putting your above code).

Position = InStr(RTrim(C.Text), ":")
Length = Len(RTrim(C.Text))


MsgBox ("position = " & Position & " Length = " & Length)

Message Box stubbornly displays:

Position = 21 Length = 22

Length is overstated by 1 !????

Can't understand what's wrong!

I even tried:

Length = Len(LTrim(RTrim(C.Text)))

Still gives 22.

No idea where the extra len comes from.
If you copy the comment to an Excel cell and apply the LEN formula, it gives 21 (correct).
But if you read directly from the comment, it gives 22 (bad)!

Leon