This may help:

Delete All Hard Returns:


Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(39), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(146) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(180), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"MRT" wrote:

Sub CleanSpecialCharacter()
Dim r As Range
For Each r In Selection
r.Value = Application.WorksheetFunction.Clean(r.Value)
Next r
End Sub

HTH
--
MRT

"Satish" wrote in message ...
Can any one help me in cleaning special characters for a selection or
range using Macro.
Currently i am using =CLEAN( ) function for each column to remove
special characters,this is almost killing my valuable time.
.