one very, very bad line of code? Trim and chr(10)

[KR]

I wanted to test to see if the Trim function would also take off any extra
chr(10)'s because I'm concatenating strings within cells and have to line up
my information with adjacent cells- but I sometimes end up with an extra
chr(10) at either the beginning or end of the cell, and I was looking for an
easy way to remove it.

I wrote the following to test to see if Trim would work (or if it only works
on regular extra spaces). The first several times I got an 'out of memory'
error, so I rebooted, and from a clean boot, tried again, and it ended up
taking over by hogging loads of memory -at least I don't have any other
explanation, since nothing else was running on the PC, and when I hard
exited Excel via the task manager my memory opened back up.

So, here's my question- why would this one line of code cause Excel to
"freak out"? Or do I have something else going on that I'm mis-attributing
to Excel? XL2003 on Win2000.

Test at your own risk :\

Sub testTrim()
Sheet4.Range("B2").Value = Trim(Str("hhh" & Chr(10)))
End Sub

 

[Jake Marx]

Hi KR,

I wanted to test to see if the Trim function would also take off any
extra chr(10)'s because I'm concatenating strings within cells and
have to line up my information with adjacent cells- but I sometimes
end up with an extra chr(10) at either the beginning or end of the
cell, and I was looking for an easy way to remove it.

Sub testTrim()
Sheet4.Range("B2").Value = Trim(Str("hhh" & Chr(10)))
End Sub

That works fine for me once I get it to compile (had to either change Str to
CStr or remove that function altogether else I get a type mismatch error).

That said, Trim() will not remove anything but trailing and leading spaces.
You can use the Replace() function to do this:

Sheet4.Range("B2").Value = Replace("hhh" & Chr(10), Chr(10),
vbNullString)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Tom Ogilvy]

Just caused an error for me since str is looking for a number argument to
convert to a string

changing it to cstr fixed it. But you don't need any conversion at all
since it is a string already

Sub testTrim()
Sheet3.Range("B2").Value = Trim(CStr("hhh" & Chr(10)))
End Sub


Sub testTrim()
Sheet3.Range("B2").Value = Trim("hhh" & Chr(10))
End Sub