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

#### leonlai

##### Board Regular
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:

Code:
``````     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

##### Board Regular
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

##### Well-known Member
Hello,

Looks like you should have ...

Code:
``[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

##### Board Regular
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

1,082,316
Messages
5,364,505
Members
400,803
Latest member
Niyetkhan

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...