Find & Replace Line Break

[Noz2k]

Hopefully an easy one this.

I would like to find and replace all line breaks within each cell in column K of my data, with a space instead.

I do this with some other characters, but am unsure how to do this with a line break. I can do this manually using the replace tool and then using either Ctrl+J or ALT 010 to type a line break, but am unsure how to do this in VBA.

For reference here is my similar code to replace all "&" with "and"

With ws1.Range("K2:K" & lngLastRow)    Set c = .find("&", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = Replace(c.Value, "&", "and")
            Set c = .FindNext(c)
        Loop While Not c Is Nothing
    End If
End With

 

[FormR]

Hi, you could try with out looping something like this:

With ws1.Range("K2:K" & lngLastRow)
    .Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart
    .Replace What:="&", Replacement:="and", LookAt:=xlPart
End With

 

[Noz2k]

Awesome! Far more efficient, thank you.