How do you cut a paste a macro or VB language in Excel and make it

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!

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" <Angela@discussions.microsoft.com> wrote in message
news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
>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!

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" <Angela@discussions.microsoft.com> wrote in message
> news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
> >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!
>
>
>

Angela

In Excel hit Alt+F11. That will open the Visual Basic Editor (VBE). In
the VBE from the menus select Insert>Module. That will create a new
module and your cursor will be near the top of the module. Paste you VBA
code right there. Then hit Alt+Q to return to Excel. In Excel from the
Menus select Tools>Macro>Macros (or Alt+F8) to open a list of macros -
I'm guessing there will be just the one. Click on the macro and select Run.

Hope this helps
Rowan

Angela wrote:
> 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" <Angela@discussions.microsoft.com> wrote in message
>>news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
>>
>>>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!
>>
>>
>>

Well - maybe I should start over. that does answer my question. Thank you,
however, my problem is not solved. Here's the situation. I imported all of
my contacts from Outlook. In the address section, there are hard returns. I
need to eliminate the hard returns and move the data beyond the HRT in to the
cell to the right. For example: "123||Anywhere Street||Suite 400||City
State". I would like to eliminate the HRT and move suite 400 in to the cell
to the right.

Thanks so much for your help and patience.

"Rowan Drummond" wrote:

> Angela
>
> In Excel hit Alt+F11. That will open the Visual Basic Editor (VBE). In
> the VBE from the menus select Insert>Module. That will create a new
> module and your cursor will be near the top of the module. Paste you VBA
> code right there. Then hit Alt+Q to return to Excel. In Excel from the
> Menus select Tools>Macro>Macros (or Alt+F8) to open a list of macros -
> I'm guessing there will be just the one. Click on the macro and select Run.
>
> Hope this helps
> Rowan
>
> Angela wrote:
> > 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" <Angela@discussions.microsoft.com> wrote in message
> >>news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
> >>
> >>>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!
> >>
> >>
> >>
>

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" <Angela@discussions.microsoft.com> wrote in message
news:D52087C1-D70E-4972-B430-F5563AAA515A@microsoft.com...
> 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" <Angela@discussions.microsoft.com> wrote in message
>> news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
>> >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!
>>
>>
>>

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 news:43976507@dnews.tpgi.com.au...
> 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" <Angela@discussions.microsoft.com> wrote in message
> news:D52087C1-D70E-4972-B430-F5563AAA515A@microsoft.com...
>> 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" <Angela@discussions.microsoft.com> wrote in message
>>> news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
>>> >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!
>>>
>>>
>>>
>
>

Angela

Just opened this. Looks like you have an address over a range of columns.
Why not use a formula?

= A1 & " " & B1 & ", " & C1 & ", " & D1

Steve

"Angela" <Angela@discussions.microsoft.com> wrote in message
news:52AAC24E-51F5-4A06-9E71-3DB202954221@microsoft.com...
> Well - maybe I should start over. that does answer my question. Thank
> you,
> however, my problem is not solved. Here's the situation. I imported all
> of
> my contacts from Outlook. In the address section, there are hard returns.
> I
> need to eliminate the hard returns and move the data beyond the HRT in to
> the
> cell to the right. For example: "123||Anywhere Street||Suite 400||City
> State". I would like to eliminate the HRT and move suite 400 in to the
> cell
> to the right.
>
> Thanks so much for your help and patience.
>
> "Rowan Drummond" wrote:
>
>> Angela
>>
>> In Excel hit Alt+F11. That will open the Visual Basic Editor (VBE). In
>> the VBE from the menus select Insert>Module. That will create a new
>> module and your cursor will be near the top of the module. Paste you VBA
>> code right there. Then hit Alt+Q to return to Excel. In Excel from the
>> Menus select Tools>Macro>Macros (or Alt+F8) to open a list of macros -
>> I'm guessing there will be just the one. Click on the macro and select
>> Run.
>>
>> Hope this helps
>> Rowan
>>
>> Angela wrote:
>> > 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" <Angela@discussions.microsoft.com> wrote in message
>> >>news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
>> >>
>> >>>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!
>> >>
>> >>
>> >>
>>

do a find and replace
look for ||

replace with cooma

then do a text to columns with the comma delimiter

--


Gary


"Angela" <Angela@discussions.microsoft.com> wrote in message
news:52AAC24E-51F5-4A06-9E71-3DB202954221@microsoft.com...
> Well - maybe I should start over. that does answer my question. Thank
> you,
> however, my problem is not solved. Here's the situation. I imported all
> of
> my contacts from Outlook. In the address section, there are hard returns.
> I
> need to eliminate the hard returns and move the data beyond the HRT in to
> the
> cell to the right. For example: "123||Anywhere Street||Suite 400||City
> State". I would like to eliminate the HRT and move suite 400 in to the
> cell
> to the right.
>
> Thanks so much for your help and patience.
>
> "Rowan Drummond" wrote:
>
>> Angela
>>
>> In Excel hit Alt+F11. That will open the Visual Basic Editor (VBE). In
>> the VBE from the menus select Insert>Module. That will create a new
>> module and your cursor will be near the top of the module. Paste you VBA
>> code right there. Then hit Alt+Q to return to Excel. In Excel from the
>> Menus select Tools>Macro>Macros (or Alt+F8) to open a list of macros -
>> I'm guessing there will be just the one. Click on the macro and select
>> Run.
>>
>> Hope this helps
>> Rowan
>>
>> Angela wrote:
>> > 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" <Angela@discussions.microsoft.com> wrote in message
>> >>news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
>> >>
>> >>>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!
>> >>
>> >>
>> >>
>>

If the hard return is the char(10) character you can skip the first step.

Data>Text to Columns>Delimited by Other. Hold ALT key and enter 0010 in
dialog box.

If HR are char(10) your data will parse.

If not, try 0013


Gord Dibben Excel MVP

On Wed, 7 Dec 2005 18:28:30 -0500, "Gary Keramidas" <GKeramidasATmsn.com>
wrote:

>do a find and replace
>look for ||
>
>replace with cooma
>
>then do a text to columns with the comma delimiter

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 news:43976507@dnews.tpgi.com.au...
> > 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" <Angela@discussions.microsoft.com> wrote in message
> > news:D52087C1-D70E-4972-B430-F5563AAA515A@microsoft.com...
> >> 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" <Angela@discussions.microsoft.com> wrote in message
> >>> news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
> >>> >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!
> >>>
> >>>
> >>>
> >
> >
>
>
>

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" <Angela@discussions.microsoft.com> wrote in message
news:342B4B51-18EE-4322-9408-DCA89DAB519E@microsoft.com...
> 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 news:43976507@dnews.tpgi.com.au...
>> > 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" <Angela@discussions.microsoft.com> wrote in message
>> > news:D52087C1-D70E-4972-B430-F5563AAA515A@microsoft.com...
>> >> 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" <Angela@discussions.microsoft.com> wrote in message
>> >>> news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
>> >>> >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!
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>

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" <Angela@discussions.microsoft.com> wrote in message
> news:342B4B51-18EE-4322-9408-DCA89DAB519E@microsoft.com...
> > 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 news:43976507@dnews.tpgi.com.au...
> >> > 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" <Angela@discussions.microsoft.com> wrote in message
> >> > news:D52087C1-D70E-4972-B430-F5563AAA515A@microsoft.com...
> >> >> 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" <Angela@discussions.microsoft.com> wrote in message
> >> >>> news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
> >> >>> >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!
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >>
>
>
>

My pleasure. Thank you for the feedback.

"Angela" <Angela@discussions.microsoft.com> wrote in message
news:62CCC155-F420-4CF7-9623-54C272178163@microsoft.com...
> 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" <Angela@discussions.microsoft.com> wrote in message
>> news:342B4B51-18EE-4322-9408-DCA89DAB519E@microsoft.com...
>> > 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 news:43976507@dnews.tpgi.com.au...
>> >> > 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" <Angela@discussions.microsoft.com> wrote in message
>> >> > news:D52087C1-D70E-4972-B430-F5563AAA515A@microsoft.com...
>> >> >> 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" <Angela@discussions.microsoft.com> wrote in message
>> >> >>> news:40C6ED30-E670-4684-9DF2-2AFE80106F72@microsoft.com...
>> >> >>> >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!
>> >> >>>
>> >> >>>
>> >> >>>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>