Concatenate By Selection

[Marshall.Harris]

I'd like to develop a function/macro to use that will allow me to
concatenate two or more cells based on the cells I select. As I scroll
down in my data, sometimes there are two cells that must be
concatenated, but sometimes it is three or more. In the end I am
hoping to come up with a simple way to highlight the cells and hit a
quick user-defined shortcut key to replace the top-most cell with the
concatenation of the cells I've selected. I'm using Excel 2007.

Can anyone help me figure this one out?

-Marshall

 

[Marshall.Harris]

Okay... I've found a nice macro to accomplish this. However, the
result ends up in the final cell as opposed to the top cell.

Sub Combine()
Dim J As Integer
If Selection.Cells.Count > 1 Then
combcont = ""
For J = 1 To Selection.Cells.Count
combcont = combcont & Selection.Cells(J).Value & " "
Selection.Cells(J).Clear
Next J
Selection.Cells(J - 1).Value = Trim(combcont)
End If
End Sub

What can I add to this macro in order to move the string to the top-
most cell of the selection?

Credit to (e-mail address removed) for the macro by the way.

-Marshall

 

[Gord Dibben]

Sub Combine()
Dim J As Integer
If Selection.Cells.Count > 1 Then
combcont = ""
For J = 1 To Selection.Cells.Count
combcont = combcont & Selection.Cells(J).Value & " "
Selection.Cells(J).Clear
Next J
Selection.Cells(1).Value = Trim(combcont)
End If
End Sub


Gord Dibben MS Excel MVP