Re: Delete Hard Return; Special Character

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
<ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> 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

.

Relevant Pages

  • Re: OT. Favorite Sad Song
    ... over and over again till both of my young boys would just roll their eyes ... and then have a party. ... Thanks for sharing that Dave ... (Gord wrote so many it's hard to pick just one. ...
    (rec.arts.mystery)
  • Re: Lost the blue title bar on top of Excel 2003 workbook
    ... Bob, Gord, and Dave: Thanks for your replys! ... Bob, the F buttons on my Logitech keyboard do not have functions assigned to ... So, no, Gord, it didn't fix it. ... name of the workbook, or move the workbook around in the window. ...
    (microsoft.public.excel.misc)
  • Re: Password Protected Excel Spreadsheet opens blank
    ... Gord & Dave, ... If this doesn't work try to re-register Excel ... Opening it in Read Only Mode. ... Dave Peterson ...
    (microsoft.public.excel.setup)
  • Re: Code to save workbook without macros
    ... Dave is correct Gord ... I had 1GB of RAM and when I set up VPC to use 512M for WinXP Pro and Vista Home ... Home Premium is at 10GB. ...
    (microsoft.public.excel.programming)
  • Re: Im trying to fit lots of text in a cell
    ... Gord, when you specify the formula, do I do that by going into Data / ... > 32,767 characters to be entered in a cell. ... unlike Excel which is not. ... >>have minimized the font, stretched the row and column, however, I still get ...
    (microsoft.public.excel.misc)