Loop through Range and Cut and Paste

K

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>
 
B

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
 
K

Kevin L

Thanks Bernie. It works perfectly.




Bernie Deitrick said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top