Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Line Breaks in VBA (Excel 2003)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Line Breaks in VBA (Excel 2003)

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

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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Line Breaks in VBA (Excel 2003)

    Or even shorter:

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

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Breaks in VBA (Excel 2003)

    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...

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Line Breaks in VBA (Excel 2003)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •