Loop through Range and Cut and Paste
 

I have a group of cells that I have selected with the mouse (these cells may
be in a row or column).

CELL1
CELL2
CELL3
CELL4
CELL5


I want to cut and paste the contents of cells 2 through 5 into Cell 1 with
soft returns between each value.

So, I would end up with Cell 1 containing (cells 2 through 5 would now be
empty):

CELL1 <SoftReturn
CELL2 <SoftReturn
CELL3 <SoftReturn
CELL4 <SoftReturn
CELL5 <SoftReturn

Loop through Range and Cut and Paste
 

Kevin,

Sub KevinMergeValues()
Dim myCell As Range
Dim myVal As String

For Each myCell In Selection
myVal = myVal & IIf(myVal = "", "", Chr(10)) & myCell.Value
Next myCell

Selection.ClearContents
Selection(1).Value = myVal
Selection(1).WrapText = True
End Sub

But this won't put a soft return after the end of the last cell's (CELL5 in
your example) value. I don't know why you would want one....

HTH,
Bernie
MS Excel MVP

"Kevin L" wrote in message
...
I have a group of cells that I have selected with the mouse (these cells
may
be in a row or column).

CELL1
CELL2
CELL3
CELL4
CELL5


I want to cut and paste the contents of cells 2 through 5 into Cell 1 with
soft returns between each value.

So, I would end up with Cell 1 containing (cells 2 through 5 would now be
empty):

CELL1 <SoftReturn
CELL2 <SoftReturn
CELL3 <SoftReturn
CELL4 <SoftReturn
CELL5 <SoftReturn

Loop through Range and Cut and Paste
 

Thanks Bernie. It works perfectly.




"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Kevin,

Sub KevinMergeValues()
Dim myCell As Range
Dim myVal As String

For Each myCell In Selection
myVal = myVal & IIf(myVal = "", "", Chr(10)) & myCell.Value
Next myCell

Selection.ClearContents
Selection(1).Value = myVal
Selection(1).WrapText = True
End Sub

But this won't put a soft return after the end of the last cell's (CELL5
in
your example) value. I don't know why you would want one....

HTH,
Bernie
MS Excel MVP

"Kevin L" wrote in message
...
I have a group of cells that I have selected with the mouse (these cells
may
be in a row or column).

CELL1
CELL2
CELL3
CELL4
CELL5


I want to cut and paste the contents of cells 2 through 5 into Cell 1
with
soft returns between each value.

So, I would end up with Cell 1 containing (cells 2 through 5 would now be
empty):

CELL1 <SoftReturn
CELL2 <SoftReturn
CELL3 <SoftReturn
CELL4 <SoftReturn
CELL5 <SoftReturn