How to remove all line breaks, leaving only single line breaks?

[leonlai]

Hi, Everybody

I want to compact all the comments on a worksheet to their minimum sizes because there are so many of them scattered everywhere on the worksheet.

One way comments may occupy too much space is when the user inserts unnecessary line feeds in the comment box.

I want to remove them with VBA.

I got this nice solution on the forum:

https://www.mrexcel.com/forum/excel-questions/873385-removing-consecutive-line-breaks.html

The code works perfectly, but it leaves me very puzzled.

The author, Rick Rothstein says his code can delete up to 12 blank lines.

I split his code into chunks to facilitate understanding:

     A = WorksheetFunction.Substitute(ctxt, WorksheetFunction.Rept(Chr(10), [B]5[/B]), Chr(10))
     B = WorksheetFunction.Substitute(A,    WorksheetFunction.Rept(Chr(10), [B]3[/B]), Chr(10))
     C = WorksheetFunction.Substitute(B,    WorksheetFunction.Rept(Chr(10), [B]3[/B]), Chr(10))
     D = WorksheetFunction.Substitute(C,    WorksheetFunction.Rept(Chr(10), [B]2[/B]), Chr(10))
    
        
     cmt.Shape.DrawingObject.Text = D

To test the code, I created a Comment with 12 blank lines (line feeds) and also text in separate lines
When I pressed the macro button, all the 12 LFs were successfully deleted!


What seems very mysterious to me:

On 1st iteration, it deletes 5 LF, replacing them by 1 LF. (Hence, Net = 4)
On 2nd iteration, it deletes 3 LF, replacing them by 1 LF. (Hence, Net = 2)
On 3rd iteration, it deletes 3 LF, replacing them by 1 LF. (Hence, Net = 2)
On 4th iteration, it deletes 2 LF, replacing them by 1 LF. (Hence, Net = 1)

Hence, the no. of blank lines deleted should be 4+2+2+1 = 9

Yet, 12 lines have been deleted!!

Can anybody explain to me what really happened?


Thanks
Leon

 

[leonlai]

I am replying to my own thread.

After being much puzzled, I found the answer.

My mistake was to believe that on the 1st iteration, it was replacing 5 LF with 1 LF.

No!

It is replacing all occurrences of 5 LF by 1 LF each time.
There are 2 groups of 5 LF available. So, it deletes 10 LF and replaces by 2LF.

Continuing the same reasoning, I can now understand how the 12 blank rows are successfully deleted!

Thanks
Leon

 

[James006]

Hello,

Looks like you should have ...

[COLOR=#ff0000]B[/COLOR][COLOR=#333333] = WorksheetFunction.Substitute(A,    WorksheetFunction.Rept(Chr(10), [/COLOR][COLOR=#ff0000][B]4[/B][/COLOR][COLOR=#333333]), Chr(10))[/COLOR]

HTH

 

[leonlai]

Hi James

I copied the formula from the forum, and wondered why the author used 3 twice.

I think you are right: should be 4.

Thanks
Leon