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.

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

On Wed, 20 Jun 2007 14:18:07 -0700, Troubled User
wrote:

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.

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

Hth,
Merjet