Hi

Try this. I think this is what you are trying to do.

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


Regards,
Per

"Per Jessen" skrev i meddelelsen
...
Hi

Set myRng = Application.InputBox(prompt:="Select cell to move data to",
Type:=8).Cells(1)

Change to Type:= 8 as shown above in all input statements.

Regards,
Per

"SteveDB1" skrev i meddelelsen
...
Per,
Thank you.
My next question.
I've tried modifying what you stated to be more generic-- each row group
will change for each occurrence.
I tried the following, and it did not work.
the error that I get is that the object variable, or with block variable
is
not set. And in setting a watch on each of my myRng_N variables, they are
"nothing."
When I placed the error elements in, they threw errors.
What have I missed here?
----------------------------------------------------------------------------------------

Dim myRng As Range
Dim myRng1 As Range
Dim myRng2 As Range
Dim myRng3 As Range
Dim myRng4 As Range
Dim myRng5 As Range


Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(prompt:="Select cell to move data to",
Type:=2).Cells(1)
On Error GoTo 0
'If myRng Is Nothing Then
'Exit Sub 'user hit cancel.
'End If

Set myRng1 = Nothing
On Error Resume Next
Set myRng1 = Application.InputBox(prompt:="Select first cell to move data
from.", Type:=2).Cells(1)
' On Error GoTo 0
'If myRng1 Is Nothing Then
'Exit Sub 'user hit cancel.
'End If

Set myRng2 = Nothing
On Error Resume Next
Set myRng2 = Application.InputBox(prompt:="Select second cell to move
data
from.", Type:=2).Cells(1)
' On Error GoTo 0
'If myRng2 Is Nothing Then
'Exit Sub 'user hit cancel.
'End If

Set myRng3 = Nothing
On Error Resume Next
Set myRng3 = Application.InputBox(prompt:="Select third cell to move data
from.", Type:=2).Cells(1)
'On Error GoTo 0
'If myRng3 Is Nothing Then
'Exit Sub 'user hit cancel.
'End If


Set myRng4 = Nothing
On Error Resume Next
Set myRng4 = Application.InputBox(prompt:="Select last cell to move data
from.", Type:=2).Cells(1)
On Error GoTo 0
'If myRng4 Is Nothing Then
'Exit Sub 'user hit cancel.
'End If

Set myRng5 = Nothing
On Error Resume Next
Set myRng5 = Application.InputBox(prompt:="Select cells to merge.",
Type:=2).Cells(1)
On Error GoTo 0
'If myRng5 Is Nothing Then
'Exit Sub 'user hit cancel.
'End If

myRng.Value = myRng.Value & myRng1.Value & myRng3.Value & myRng4.Value

myRng2.ClearContents
myRng3.ClearContents
myRng4.ClearContents

myRng5.Select
With Selection
.MergeCells = True
End With

End Sub
-----------------------------------------------------------------------

Best.


"Per Jessen" wrote:

Hi

Suppose you want to edit contents in A1

Range("A1").Value = Range("A1").Value & Range("B1").Value
Range("B1").ClearContents

Hopes it helps.

Regards,
Per

"SteveDB1" skrev i meddelelsen
...
Hi all.
We have a number of workbooks which list documents, in a form which
we're
calling an abstract of title.
In our older workbooks, many of the abstracts list the sellers, and
buyers
with 4 row, single column groups. all as unmerged cells.

With our newer formats, we now have these 4 row, 1 column groups
merged
into
a single cell. It just makes for a cleaner appearing layout.

This morning I tried recording a macro to select row 2, and paste its
contents into row 1 with already existing data. I then selected row 3,
and
pasted that cell's contents into those of row 1, and did the same with
row
4.

Once this was complete, I merged the 4 rows, and turned on word wrap.
The problem that I saw once I went in to edit the code was that it
treated
the contents as an ActiveCell.FormulaR1C1.

Which of course gives the string of contents.

I then tried recording another macro to do the same thing by
copy/paste,
and
cut/paste. Once I activated the destination cell, to paste the
contents of
my
source cell, the cut/copy deactivated, and would not allow me to paste
the
source contents to my destination cell.

As I need this to be more generic to cover all instances of this, I'm
curious as to what else I can use to accomplish this same goal,
without
over
writing the destination cell's existing contents.

How do I select a cell, cut its contents, and paste those contents
into a
cell with already existing contents-- without overwriting those
contents--
by
use of a macro?

The merging I can handle.
Thank you for your helps.