Loop through Range and Cut and Paste

[Kevin L]

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>

 

[Bernie Deitrick]

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]

Thanks Bernie. It works perfectly.

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