Hard Returns

[Troubled User]

I am moving data to Excel from SQL that contains hard returns in these text
fields. I need to remove the hard return values (they look like little
boxes) using VBA. I can do it manually by activating the cell and simply
deleteing them. The hard return still exists even when the symbol is
removed. That is what I need to do in code.

Thanks in advance.

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[merjet]

Chr(127) is "return." So you could loop thru the cells and delete it
when found.

Hth,
Merjet