Prev: Replace manual line breaks (Alt+Enter) with two spaces for selected cells
Next: String of numbers
From: joel on 21 Mar 2010 07:26

The return in the cell is code as an ascii character Linefeed which is
a 10. In the code below I set a variable to character 10. You can also
use vblf. You can use any string method with the linefeed like REPLACE
to remove the character or replace the character with other characters

LF = Chr(10)
or
LF = vbLF

with
Set c = .Columns("A").Find(what:=LF, _
LookIn:=xlValues, lookat:=xlPart)
if not c is nothing
FirstAddr = c.address
do
c = replace(c,LF, " ")
set c = .Columns("A").findnext(after:=c)
while not c is nothing and c.address <> FirstAddr

end if
end with


The code below is complicated. I just wanted to show a couple of
different methods. I don't know how many cells you are trying to replce
the Linefeed with two spaces. You can achieve the same thing as
follows

LF = vbLF

with sheets("sheet1")
.columns("A").replace(LF," ")

end with


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=189290

http://www.thecodecage.com/forumz/chat.php

From: andreashermle on 21 Mar 2010 16:17
On Mar 21, 12:26 pm, joel <joel.486...(a)thecodecage.com> wrote:
> The return in the cell is code as an ascii character Linefeed which is
> a 10. In the code below I set a variable to character 10.  You can also
> use vblf.  You can use any string method with the linefeed like REPLACE
> to remove the character or replace the character with other characters
>
> LF = Chr(10)
> or
> LF = vbLF
>
> with
> Set c = .Columns("A").Find(what:=LF, _
> LookIn:=xlValues, lookat:=xlPart)
> if not c is nothing
> FirstAddr = c.address
> do
> c = replace(c,LF, "  ")
> set c = .Columns("A").findnext(after:=c)
> while not c is nothing and c.address <> FirstAddr
>
> end if
> end with
>
> The code below is complicated.  I just wanted to show a couple of
> different methods.  I don't know how many cells you are trying to replce
> the Linefeed with two spaces.  You can achieve the same thing as
> follows
>
> LF = vbLF
>
> with sheets("sheet1")
> .columns("A").replace(LF,"  ")
>
> end with
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:http://www.thecodecage.com/forumz/showthread.php?t=189290
>
> http://www.thecodecage.com/forumz/chat.php

Hi Joel,

great help. Thank you very much for your professional advice. It works
as desired.

Regards, Andreas

 | 
Pages: 1
Prev: Replace manual line breaks (Alt+Enter) with two spaces for selected cells
Next: String of numbers