Clean and Trim data from a column

[Deepk]

Hi,

I am trying to clean or trim my data in a column. My objective is to remove all non-printable characters and also the leading and tailing extra spaces or the ALT-enters. The code I have written also removes ALT-enters in the middle of the data . Please find my code below:

Sub CleanTrim()


Dim cell As Range

On Error GoTo ErrHandling


Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
On Error Resume Next
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
cell.Value = Application.WorksheetFunction.Clean(cell.Value)
Next cell

ErrHandling:
If Selection.Columns.Count > 1 Or Selection.Rows.Count > 10000 Then
MsgBox Prompt:="Please verify the data you have selected", Title:="Wrong selection!"
Else
MsgBox "Process Completed Successfully."
End If

End Sub

Request you to kindly update this code. Thank you.

 

[Deepk]

hey,

please help me with this. I want clean function to replace chr(10) or alt-enter at the start and end of the string not in the middle of the string.