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

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
<(E-Mail Removed)> 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--

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

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
> <(E-Mail Removed)> 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--
>
>

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
> > <(E-Mail Removed)> 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

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
> > > <(E-Mail Removed)> 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
>