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

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
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
Joined
Mar 22, 2019
Messages
77
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
Joined
Apr 4, 2009
Messages
3,680
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
Joined
Mar 22, 2019
Messages
77
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top