Is it possible to delete vbLf in all comments, but only those positioned after the last visible character?

[leonlai]

Hi, Everybody

I want to make my Excel comments occupy as little space as possible, since my worksheet may contain so many.

So, I remove the User Name in all comments if they were created using "Insert Comment"

I also want to remove all spaces represented by vbLf (if user hits Enter).

However, I do not want to delete all vbLf characters, otherwise if a user enters a multi-line comment, it will be made single-lined.

For example if the comment is like this:

Missing Goods:
2 crates Coca Cola
50 kg meat

To phone Johnny to sort out


---

I want the comment to retain its multi-line character, like this:

Missing Goods:
2 crates Coca Cola
50 kg meat
To phone Johnny to sort out

But not like this:
Missing Goods:2 crates Coca Cola50 kg meatTo phone Johnny to sort out

In other words, I would like to delete only those vbLF after the last visible character (out) . This may happen if the user hits Enter after typing his comment.

And, if possible (and if not too complicated to do), to delete the blank lines between the occupied lines.

Here is my existing code:

Sub FormatAllComments()


  Dim ws As Worksheet
  Dim cmt As Comment
  Dim User As String
  User = Application.UserName & ":" & vbLf
  
   
  For Each ws In ActiveWorkbook.Worksheets
    For Each cmt In ws.Comments
 
    cmt.Text Text:=Replace(cmt.Text, User, "")
   
              
    Next cmt
  Next ws
End Sub

Thanks for any help.

Leon


NB: RTRIM does not seem to work.

 

[igold]

This would would isolate a VBLF character that appears at the end of a comment as the last character, you can apply it as you see fit...

Asc(Mid(C.Text, Len(C.Text), 1)) = 10

 

[leonlai]

Hi, igold

Thanks for your reply.

I have tried unsuccessfully to incorporate your code into mine.

Could you please modify my code to make it work?

Best Regards,
Leon

 

[igold]

Something like this...

Sub FormatAllComments()


    Dim ws As Worksheet
    Dim cmt As Comment
    Dim User As String, ctxt As String
    Dim cha As Integer


    User = Application.UserName & ":" & vbLf
    For Each ws In ActiveWorkbook.Worksheets
        For Each cmt In ws.Comments
            ctxt = cmt.Text
            cha = Asc(Mid(ctxt, Len(ctxt), 1))
            If cha = 10 Then cmt.Shape.DrawingObject.Text = Left(ctxt, (Len(ctxt) - 1))
        Next cmt
     Next ws
     
End Sub

 

[leonlai]

Hi, igold

Your code works wonderfully.

It took me nearly half day to figure out how it works, and I learned a lot just by studying it:

10 is the ASCII code for Line Feed character (Enter)
Asc function returns the first character in the string.
You have used a clever trick (using Mid) to check if the last character in the comment is a LF character.

However, your code removes only one LF character.

I modified your code slightly to make it remove all LF characters at the end of the comment. Just in case the user hit Enter several times.

For Each cmt In ActiveSheet.Comments
  
     ctxt = cmt.Text
     cha = Asc(Mid(ctxt, Len(ctxt), 1))
         
     [B]Do While cha = 10[/B]
     
     ctxt = cmt.Text
     cha = Asc(Mid(ctxt, Len(ctxt), 1))
     If cha = 10 Then cmt.Shape.DrawingObject.Text = Left(ctxt, (Len(ctxt) - 1))
     
     [B]Loop
 [/B]
 Next cmt

I am pleasantly surprised at the result. I hope there are no errors in the modified code.

Best Regards,
Leon

 

[igold]

I am glad it worked for you. Although I was not thinking clearly when I wrote it.

Instead of using the Mid function, the Right function would work as well. The same way I used the Left function...

For Each cmt In ActiveSheet.Comments
  
     ctxt = cmt.Text
[COLOR=#ff0000]     cha = Asc(Right(ctxt, 1))[/COLOR]
         
     Do While cha = 10
     
     ctxt = cmt.Text
[COLOR=#ff0000]     cha = Asc(Right(ctxt, 1))[/COLOR]
     If cha = 10 Then cmt.Shape.DrawingObject.Text = [COLOR=#ff0000]Left(ctxt, (Len(ctxt) - 1))[/COLOR]
     
     Loop
 
 Next cmt

At any rate, I was happy to help and thanks for the feedback!

 

[James006]

Hello,

You probably adapt to the cell's comment the following count of Breaks ...

Sub TestNbBreaksInCell()
Dim c As Range
Set c = ActiveCell
MsgBox Len(c.Value) - Len(Application.WorksheetFunction.Substitute(c.Value, Chr(10), vbNullString)) & " Linebreak(s)"
End Sub

Hope this will help

 

[igold]

@James,

The OP only wants to remove the VBLF that appear at the end of the comment, not all of them.

 

[James006]

@James,

The OP only wants to remove the VBLF that appear at the end of the comment, not all of them.

Sorry ...

Quickly read the first message ... and not even seen you had posted answers ...!!!

 

[igold]

No problems, I do that all the time...