delete Blank lines in cell

[Clare1805]

I have a large spreadsheet of 1000+ rows showing details of ICT issues logged on a service desk. Column B is text and therefore contains blank lines within the cell - is there anyway of removing these blank lines by means of a formula/macro?

<TABLE style="WIDTH: 176pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=234 border=0 x:str><COLGROUP><COL style="WIDTH: 176pt; mso-width-source: userset; mso-width-alt: 8557" width=234><TBODY><TR style="HEIGHT: 76.5pt; mso-height-source: userset" height=102><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 176pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 76.5pt; BACKGROUND-COLOR: transparent" width=234 height=102>for example

this cell contains blank rows

within the cell</TD></TR></TBODY></TABLE>

 

[Sektor]

Sub RemoveBlankLines()
    
    Dim cell As Range
    
    For Each cell In Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        cell = Replace(cell, Chr(10), "")
    Next
    
End Sub

 

[Clare1805]

That worked a treat - thanks Sektor.

 

[Peter_SSs]

Rather than doing them a row at a time, you could do them all at once.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> RemoveBlankLines_v2()<br>    Columns("B").Replace What:=Chr(10), Replacement:="", _<br>        LookAt:=xlPart, SerachFormat:=False, ReplaceFormat:=False<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Depending on just what your data is like, you might find a better result if you include a space in the 'Replacement' text.

 

[marcnz]

Hi,

Actually it is not exactly what I would need. It does remove all the return carriages, but it does it to all the lines in the cells. This means that you end up with all the line with content in the same row, just separated by a space.

How can I remove the blank lines only where there is no other characters on it?

Thank you.

Sub RemoveBlankLines()
    
    Dim cell As Range
    
    For Each cell In Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        cell = Replace(cell, Chr(10), "")
    Next
    
End Sub

 

[Peter_SSs]

Hi,

Actually it is not exactly what I would need. It does remove all the return carriages, but it does it to all the lines in the cells. This means that you end up with all the line with content in the same row, just separated by a space.

How can I remove the blank lines only where there is no other characters on it?

Thank you.

It sounds to me like your cells may contain two successive carriage returns. Allowing for that, and the typo I had in my earlier suggestion, see if this does what you want.

Sub RemoveBlankLines_v2()
    Columns("B").Replace What:=Chr(10) & Chr(10), Replacement:=Chr(10), _
        LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[marcnz]

Hi Peter,

Perfect, thank you so much.

It sounds to me like your cells may contain two successive carriage returns. Allowing for that, and the typo I had in my earlier suggestion, see if this does what you want.

Sub RemoveBlankLines_v2()
    Columns("B").Replace What:=Chr(10) & Chr(10), Replacement:=Chr(10), _
        LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[Peter_SSs]

Hi Peter,

Perfect, thank you so much.

Great, thanks for letting us know.

 

[marcnz]

The basic does work as expected.

However, the task I had on hand required much more work than just that. Interestingly, a user had the job to export a whole bunch of Outlook emails and to clean up the "body" of the messages that end up in the Excel cells.

So it is unpredictable how many blank lines there are, how many blank spaces even in blank lines there are, etc.

My final solution:
1. Split the content of the body cell (all in column "C") into an array
2. Split each line in the array by words
3. Check the length of the line.
4. Length greater than 0, check if any of the word are in a list of alphanumeric characters, including some special characters
4. do while loop and every double blank space, put only one. Recursive action until no more double ups.
5. if yes, the line is a good line to keep - Adding it to the cell lines collection (no need to adjust its size, easier to work with)
6. dump the final collection with a chr(10) so each line on its own into the cell.

I don't have the final code with me as I am no longer at work, but if you want it, I am happy to post it in the morning if you think it can help someone.

Again, your solution answered the main question. You were not aware of what it was to be applied to.

Thank you.

Great, thanks for letting us know.

 

[Rick Rothstein]

So it is unpredictable how many blank lines there are, how many blank spaces even in blank lines there are, etc.

My final solution:
1. Split the content of the body cell (all in column "C") into an array
2. Split each line in the array by words
3. Check the length of the line.
4. Length greater than 0, check if any of the word are in a list of alphanumeric characters, including some special characters
4. do while loop and every double blank space, put only one. Recursive action until no more double ups.
5. if yes, the line is a good line to keep - Adding it to the cell lines collection (no need to adjust its size, easier to work with)
6. dump the final collection with a chr(10) so each line on its own into the cell.

The following code will be much faster and will effectively collapse from 2 up to as many as 9840 consecutive line feeds in any cell in Column B down to single line feeds for each set of consecutive line feeds in the cells...

Sub RemoveBlankLines_V3()
  Dim V As Variant
  For Each V In Array(121, 13, 5, 3, 3, 2)
    Columns("B").Replace What:=String(V, vbLf), Replacement:=vbLf, LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
  Next
End Sub