Delete Hard Return; Special Character

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm looking for a simple macro that deletes all hard returns in all cells in
a large worksheet. These hard returns originally came from Outlook's BCM.
In Excel, the characters look like a small square, and when copied and pasted
into Word, they look like this "^" or even this "^l". Does anyone have any
idea how to delete all such characters on a sheet, or all such characters in
a specified range (A1:CA6000).

Regards,
Ryan--
 
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


Gord Dibben MS Excel MVP
 
no macro needed:

1. click on A1
2. pull-down:

Edit > Replace

in the Find what block touch CNTRL-j
leave the Replace with block empty

click Replace all
 
Thanks for the look! Gary''s Student, I know this trick, and I’ve used it
successfully many times in the past. Unfortunately, it does not work in this
instance. Gord Dibben, I tried your macro. I had to modify it a bit. I ran
the following:
Sub Remove_CR_LF()
With Selection
Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

I got a message saying “Compile Error: Named argument not found.â€

Any more ideas?
TIA,
Ryan--
 
Take a look at Gord's suggestion once more.

You'll notice that he had a dot in front of "Replace" (".Replace"). That means
that this method is refering to the object in the previous "with" statement--in
this case, it's the Selection.

So add that dot back!
 
Gord and Dave, you are 100% correct! Your guidance was great!! I think I
(somehow) became the victim of word wrap. The macro worked great when I took
another look and actually tried to understand what was going on!! The more I
learn, the more I realize there is a lot left to learn!!

Regards,
Ryan---
 
Back
Top