Excel remove ONLY trailing carriage returns

[bluemarlin]

I'm trying to remove only the trailing carriage returns from my data. Sometimes there's 1, sometimes 2 or more. I don't want to remove other line breaks, only the trailing ones. Can anyone kindly assist?

 

[Yongle]

Try this method on a COPY of your data

Sub RemoveTrailingLineFeed()

    Dim myStr As String, JobIsDone As Boolean, Cel As Range

    For Each Cel In Range("A1:A100")
        If Cel.Value <> "" Then
            myStr = Cel.Value
            JobIsDone = False
            Do Until JobIsDone
                Select Case Right(myStr, 1)
                    Case Chr(13), Chr(10)
                        myStr = Left(myStr, Len(myStr) - 1)
                    Case Else
                        JobIsDone = True
                End Select
            Loop
            Cel = myStr
        End If
    Next Cel
    
End Sub

 

[bluemarlin]

Thanks so much Yongle, this works perfectly. You have saved me LOTS of time!

Try this method on a COPY of your data

Sub RemoveTrailingLineFeed()

    Dim myStr As String, JobIsDone As Boolean, Cel As Range

    For Each Cel In Range("A1:A100")
        If Cel.Value <> "" Then
            myStr = Cel.Value
            JobIsDone = False
            Do Until JobIsDone
                Select Case Right(myStr, 1)
                    Case Chr(13), Chr(10)
                        myStr = Left(myStr, Len(myStr) - 1)
                    Case Else
                        JobIsDone = True
                End Select
            Loop
            Cel = myStr
        End If
    Next Cel
    
End Sub