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.
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
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.