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" <no_spam@not_real_email.com> wrote in message
news:[email protected]...
> 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>
>
>
>
>
>

[Kevin L]

Thanks Bernie. It works perfectly.




"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:u%[email protected]...
> 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" <no_spam@not_real_email.com> wrote in message
> news:[email protected]...
>> 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>
>>
>>
>>
>>
>>
>
>