Oh My Steve - that totally worked. I t returned a character 13 and and just
edited the original macro and replaced 10 with 13 and it toally worked.

Thanks so very much for all of your help!

"Steve" wrote:

Hi Angela

Solving problems can be a trial at times. You're starting to see the peverse
reasons we enjoy it!!

The code stripped all [Alt]/[Enter] characters when I tested it. I can only
presume there's a different character in the string.

Select one of the values you're having trouble with then run this:


Sub WhatCharIsIt()
' places stripped value in cell to right
' advises user of non-printable characters
Dim tmp, testchar, rtnval, i
tmp = Selection.Value
For i = 1 To Len(tmp)
testchar = Mid(tmp, i, 1)
If Asc(testchar) < 32 Then
' 0 to 31 are non-printable ASCII codes
MsgBox testchar ' msg box the unprintable code
Else
rtnval = rtnval & testchar
End If
Next i
Selection.Offset(, 1).Value = rtnval
End Sub


You should be able to substitute any number returned in the macro below (my
guess is it will return 13).


If it returns nothing, copy and paste only the unwanted value into the macro
in place of Chr(10). When you do, make sure you wrap it in double quotes.
Better yest, repeat the replace code then substitute. You'll end up with
something like:

....
c.Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

c.Replace What:=Chr(13), Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

c.Replace What:="|", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
....

When you've worked it out, this is faster code than the original so, add you
modification to this.
Sub ReplaceHardRtn()
' replaces character created by Alt/Enter
' select the range with the hard returns first
' Leaves values in the same location.
With Selection
.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

--
Steve


"Angela" wrote in message
...
Thanks everyone so much!

Steve: That worked, however, it seemed to only remove one of the || from
the cells. It is as though one of the HRT still exists and the macor
won't
remove it.
Any ideas?

"Steve" wrote:

Angela

You may need to modify
Replacement:="" to
Replacement:=" "

The former was a zero length character. The latter is a blank character.

Example:

abc
def

the former result in "abcdef"
the latter results in "abc def"

Happy macroing

Steve

"Steve" <No Spam wrote in message ...
Angela

That's a totally different problem. If you mean the returns created by
pressing [Alt]/[Enter], cut/paste won't fix it.
Presseing [Alt]/[Enter] creates a special character that causes Excel
to
go to a new line. In VBA, this is character 10 (Chr(10)). This
character
cots and pastes with other values.

The following will strip Chr(10) from the cells you select:.

Sub ReplaceHardRtn()
' replaces character created by Alt/Enter
' select the range with the hard returns first
' Leaves values in the same location.
For Each c In Selection
c.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next c
End Sub


Use Rowan's instructions to put this into a module and run it.

Test it on a single cell first to make sure it does what you want. If
happy, select whatever range you want cleaned then run it.



Steve

"Angela" wrote in message
...
Thanks - but actually, the code was to remove hard returns in cells.
I
do
not know how to run the code in VB.

"Steve" wrote:


Sub Macro1()

'recorded with Macro recorder
Range("A1:A2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
End Sub

Sub CutMethod()

' looking up 'Cut Method' in help and modifying Example
Worksheets("Sheet1").Range("A1:A2").Cut Range("B1:B2")

End Sub

"Angela" wrote in message
...
I have some code that soemone sent me to cut and paste as a macro to
performa
function. I do not know how to do that. I can get to the VB
window,
but
I
don't knwo what do do from there.

Help!