modification of Per Jessen Macro- paste/overwrite

[SteveDB1]

Hi Per,
I have found an item within your last macro to me that I'd like to modify.

What I'd like to do is to have a chr(10) inserted between each of the
original elements.

What I've tried thus far has resulted in either the placement of the chr(10)
before, or after the text strings, but not in the middle of.

How do I get it to place the chr(10) in between each line of the original
contents?

'---------------------------------------------
For cell = 1 To myRng.Cells.Count
NewString = NewString & myRng.Cells(cell, 1).Value & " "
myRng.Cells(cell, 1).ClearContents
Next
'-----------------------------------
I've tried placing the chr(10) before the NewString, before the
myRng.cells(cell,1).Value, and then before the two double quotes.
If I place it before the NewString after the = sign, it inserts as many rows
as are merged. Which makes sense.
if I place it before the myRng.cells()..... if places a single row above the
initial text,
and then it does as I want-- a chr(10) at the end of each subsequent row.
How can I get it to skip the insertion to the first row, and only do
subsequent
rows?
Best.
Thank you.

'--------------------------------------------
Dim myRng ' As Range
Dim NewString As String

Sub steven()

Set myRng = Nothing
On Error Resume Next
InputRange:
Set myRng = Application.InputBox(prompt:="Select cells to concatenate",
Type:=8)
If myRng Is Nothing Then End

If myRng.Cells.Count > 5 Then
msg = MsgBox("Invalid range selection" & vbLf & vbLf & "Please try
again", vbExclamation, "Regards, Per Jessen")
GoTo InputRange
End If

For cell = 1 To myRng.Cells.Count
NewString = NewString & myRng.Cells(cell, 1).Value & " "
myRng.Cells(cell, 1).ClearContents
Next

myRng.Cells(1, 1) = NewString
myRng.MergeCells = True

End Sub

 

[Per Jessen]

Hi Steve

I use vbLf instead of chr(10) (same result), and test if NewString is empty.
Suppose you don't need the two double quotes any more.

For cell = 1 To myRng.Cells.Count
If NewString = "" Then
NewString = myRng.Cells(cell, 1).Value
Else
NewString = NewString & vbLf & myRng.Cells(cell, 1).Value
End If
myRng.Cells(cell, 1).ClearContents
Next

Best regards,
Per

 

[SteveDB1]

Hi Per,
It works Per-fectly.
No malice intended, as I'm sure you've heard that 10000 times or more.
Excellently done.
And I looked up the vbLf in the help file. I didn't know about that. I've
printed it out for ready accessibility.
Thank you again for your help/code.
Best Regards.