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

[Guest]

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!

 

[Steve]

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

 

[Guest]

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

 

[Rowan Drummond]

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

 

[Guest]

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.

 

[Steve]

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

 

[Steve]

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

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

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

 

[Steve]

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

 

[Gary Keramidas]

do a find and replace
look for ||

replace with cooma

then do a text to columns with the comma delimiter

 

[Gord Dibben]

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

 

[Guest]

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?

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

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

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

:


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

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!

 

[Steve]

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

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?

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

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

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

:


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

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!

 

[Guest]

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!

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

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?

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

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

:


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

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!

 

[Steve]

My pleasure. Thank you for the feedback.

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!

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

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?

:

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

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

:


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

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!