Why Doesn't VBA replace function work with CRLF in Word and Excel

chr, replace, vba

Solution

The premise is flawed:

myString = "ABC" & vbCrLf & "DEF"

The string is made of "ABC", `vbCrLf`, and "DEF".

`vbCrLf` is `vbCr` and `vbLf`, which on any Windows box is `vbNewLine`.

When you do:

str1 = Replace(myString, vbLf, "")

You replace `vbLf` and leave the `vbCr` character in place.

str2 = Replace(str1, vbCrLf, "")

Then you replace `vbCrLf` but `vbLf` is already gone so `vbCrLf` isn't in the string.

str3 = Replace(str2, vbNewLine, "")

Then you replace `vbNewLine` which is basically doing the exact same thing as the previous instruction, and the result is a string that's been stripped of `vbLf` but still contains `vbCr`.

This code works as expected:

Sub Test()
    Dim foo As String
    foo = "foo" & vbCrLf & "bar"
    Debug.Print foo
    foo = Replace(foo, vbNewLine, vbNullString)
    Debug.Print foo
End Sub

As does this:

Sub Test()
    Dim foo As String
    foo = "foo" & vbNewLine & "bar"
    Debug.Print foo
    foo = Replace(foo, vbNewLine, vbNullString)
    Debug.Print foo
End Sub

Or this:

Sub Test()
    Dim foo As String
    foo = "foo" & vbNewLine & "bar"
    Debug.Print foo
    foo = Replace(foo, vbCrLf, vbNullString)
    Debug.Print foo
End Sub

Or even this:

Sub Test()
    Dim foo As String
    foo = "foo" & vbNewLine & "bar"
    Debug.Print foo
    foo = Replace(foo, vbCr, vbNullString)
    foo = Replace(foo, vbLf, vbNullString)
    Debug.Print foo
End Sub

Your second snippet works as intended, because you do remove both `vbCr` (`Chr(13)`) and `vbLf` (`Chr(10)`) characters. Simple as that.

Problem

I could have sworn I have stripped CRLF in the past but not sure why the following isn't working: ``` myString = "ABC" & vbCrLf & "DEF" str1 = Replace(myString, vbLf, "") str2 = Replace(str1, vbCrLf, "") str3 = Replace(str2, vbNewLine, "") MsgBox str3 ``` The code above doesn't work the result is: ``` ABC DEF ``` ``` myString = "ABC" & vbCrLf & "DEF" str1 = Replace(myString, Chr(13), "") str2 = Replace(str1, Chr(10), "") MsgBox str2 ``` The code above does work the result is: ``` ABCDEF ``` Solution: Thanks @ Mat for the answer (The problem on the first code was the order I was trying to remove the items) VbCrLf & VbNewLine is the same and trying to remove the combo vbCr+VbLf after removing VbLf won't work

Original source