Remove names from comments with vba code

Hi,
At work we have sheets where several users enter comments and these all by default show their names in bold. I am hoping someone is able to help me with a little bit of code.

Currently I have a sub which finds in all comments certain text (eg "My Name:") and replaces it with blank (""). This works fine except when i tested this it formatted the remaining font bold and leaves a blank line at the top if the comment (ie where the users name was). I have added to the code so that it then runs through all comments again and sets the text style to 'normal'.

All I need now is help on removing the blank line at the top of the comment.

Note I know nothing about VBA, I have pieced the below code from various places. If anyone has a easier way to remove names in comments (after comments have been made) then Im willing to try.

Please Login or Register  to view this content.

Thanks in advance!
Harribone

Hi, Harribone,

anyhow that will leave the name of the person who inserted the comment into the sheet still visible in the statusbar.

Try using

Please Login or Register  to view this content.

And maybe adjust the line concerning the font used to

Please Login or Register  to view this content.

Ciao,
Holger

Sorry just noticed that I posted "2011" and "2012" in my code which was from the original source. This will read as (for example):

Please Login or Register  to view this content.

The status bar doesn't matter, am happy to have names showing there. The worksheet(s) we use have 1000's of comments and I just want them to look tidy by removing names from the top of the comment text. The suggestion of FontStyle set to 'Standard' does exactly the same as 'Normal' as far as my testing shows which is fine because this is what I want it to do anyway.

The problem I am having with the code at the moment is that it leaves a blank line above the comment text:

Before:

1 Harribone:
2 This is a comment
3 I have typed

After sub ran:

1
2 This is a comment
3 I have typed

The 1,2,3 reprsent each line in the comment. When the name (Harribone: ) is replaced with "" it leaves a blank line. Do I need to modify the code in place or add more code to remove the top line?
If this is possible.

I have recorded a macro to see what the text looks like in code form (Cell D6):

With the blank line -

Please Login or Register  to view this content.

Without blank line:

Please Login or Register  to view this content.

I'm clueless on writing code as I'm still working my way through excel formulas at the moment. Hopefully there is a way of looping through all comments on a sheet, store/extract the text in the comment, trim the "" & Chr(10) & from it then change the comment text to the new trimmed value.

Hi, Harribone,

no problem about the years but there is still a difference in your code

Please Login or Register  to view this content.

from what I proposed

Please Login or Register  to view this content.

which includes the line feed to search and replace and thus left no empty row in my test with Excel2013.

Ciao,
Holger

Ahhh I see! sorry I misunderstood what that was for, feel like a bit of a fool now :P

Help much apriciated on this!