Remove Hard Carriage Return And Leading And Trailing Spaces
Mr. Rick,
Looking for a solution to my situation, I have found this 2013 post: https://www.mrexcel.com/forum/excel-...e-returns.html. I have tried to post my question here but it seems to be old and required to have a new post.
Your solution works great from me to remove hard carriage returns. I would like to see if possible to incorporate this to an existing private sub I am using to trim spaces from start and end of entered text strings.
My code is the following:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("A4:A1004")) Is Nothing Then
Application.EnableEvents = False
For Each Cell In Intersect(Target, Range("A4:A1004"))
If Len(Cell.Value) Then Cell.Value = Trim(Cell.Value)
Next
Application.EnableEvents = True
End If
End Sub
I would like to include your solution to above mentioned, so that when entering information at the target range, the macro automatically removed the hard carriage returns as well
Code:
Sub ReplaceAtSignWithLineFeed_v3()
Selection = Evaluate("IF(ROW(),SUBSTITUTE(" & Selection.Address & ",CHAR(10),""""))")
End Sub
By the way, my first macro portion takes some time to execute, if you can suggest a more effective and expedited way to do it, please let me know.
Thanks in advance for your assistance.
