Line Breaks in VBA (Excel 2003)

Topic

New Reply

A warm hello on a cold day in New York to all you Excel gurus…

What I need to do is loop through cells in a range (say a column) and replace every instance of two consecutive line breaks with one line break, something like Chr(10)Chr(10) being replaced by Chr(10). This is to delete blank lines within multiline cells.

I know this is a simple task in Word, but I can’t seem to come up with anything that works in Excel.

As always, I am grateful and thankful for any and all advice.

Reply | Quote

Replies

Something like this (the example is for column D):

Sub RemoveDoubleLinebreaks()
Dim oCell As Range
For Each oCell In Range(“D:D”).SpecialCells(xlCellTypeConstants)
oCell = Replace(oCell, vbLf & vbLf, vbLf)
Next oCell
End Sub

Reply | Quote

Or even shorter:

Sub RemoveDoubleLinebreaks()
Range(“D:D”).Replace What:=vbLf & vbLf, Replacement:=vbLf, LookAt:=xlPart
End Sub

Reply | Quote

Hi Hans,

Thank you, as always, for your able assistance.

I’m a little embarrassed to admit that I actually had created similar code myself, but it didn’t seem to work on an instance I found in my data, so I thought I was missing something. It turns out that the code does work on Alt+Enter returns, but in this one cell there is some other kind of return (it’s downloaded data) that the code doesn’t affect.

So there is nothing wrong with the code. The problem is within the data.

Thanks again. Very much appreciated…

Reply | Quote

You could try vbCrLf instead of vbLf, or just vbCr.

Reply | Quote