Copy from many cells and paste to one

[eoreality]

I need to copy a range of cells (C7:C20) and past the contents into one
cell (B4). the concatenate function is not the solution because I need
the results to appear as separate lines in the cell (B4) when pasted.
(leaving a "return" between lines)

Weird I know but Ahem, "It's for a friend".


Thanks,

Robert

 

[Dave Peterson]

J.E. McGimpsey has a User defined function that will concatenate cells.

You can find it at:
http://www.mcgimpsey.com/excel/udfs/multicat.html

You'll use it in the worksheet like:

=MultiCat(c7:c20,char(10))

J.E. shows this as his example: =MultiCat(A1:C1," ")

Char(10) is that alt-enter that you want.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gord Dibben]

Robert

You could do this with a formula.

=C7 & CHAR(10) & C8 & CHAR(10) etc. until C20.

Or you could use a macro to do it.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox _
("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) > 0 Then sbuf = sbuf & y.text & w & Chr(10)
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

You probably don't need a delimiter in your case, so don't enter anything in
that inputbox.


Gord Dibben Excel MVP