Delete Hard Return; Special Character
 

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--


--
RyGuy

Delete Hard Return; Special Character
 

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

On Tue, 2 Oct 2007 14:09:29 -0700, ryguy7272
wrote:

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--

Delete Hard Return; Special Character
 

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
--
Gary''s Student - gsnu200748


"ryguy7272" wrote:

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--


--
RyGuy

Delete Hard Return; Special Character
 

Thanks for the look! Gary''s Student, I know this trick, and Ive 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--




--
RyGuy


"Gord Dibben" wrote:

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

On Tue, 2 Oct 2007 14:09:29 -0700, ryguy7272
wrote:

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--

Delete Hard Return; Special Character
 

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!

ryguy7272 wrote:

Thanks for the look! Gary''s Student, I know this trick, and Ive 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--

--
RyGuy

"Gord Dibben" wrote:

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

On Tue, 2 Oct 2007 14:09:29 -0700, ryguy7272
wrote:

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--



--

Dave Peterson

Delete Hard Return; Special Character
 

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---

--
RyGuy


"Dave Peterson" wrote:

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!

ryguy7272 wrote:

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--

--
RyGuy

"Gord Dibben" wrote:

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

On Tue, 2 Oct 2007 14:09:29 -0700, ryguy7272
wrote:

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--



--

Dave Peterson