remove crlf

[totalbiz]

I am new to VBA. I need to select a column in the worksheet and remove all
carriage return/line feeds. I am using the following code I got off
different posts to this group. What happens is the loop runs forever. What
am I doing wrong?

Dim aCell As Range
Sub Remove_CR_LF()
For Each aCell In Selection
With Selection
.Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
Next aCell
End Sub

 

[Dave Peterson]

It shouldn't run forever. It should only run for the number of cells in your
selected area.

But you're doing too much. You don't need to cycle through each cell. You can
select the range and do it in one Edit|replace (or VBA equivalent).

Option Explicit
Sub Remove_CR_LF()

Dim myRng as range
set myrng = activecell.entirecolumn

myrng.replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

End Sub

I'd use:

myrng.replace What:=vbcrlf, Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

I think it's more self-documenting.