From: Marshall.Harris on
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
From: Marshall.Harris on
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 stefi(a)discussions.microsoft.com for the macro by the way.

-Marshall

On Dec 16, 2:41 pm, "Marshall.Harris" <marshall.d.har...(a)gmail.com>
wrote:
> 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

From: Gord Dibben on
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


On Wed, 16 Dec 2009 12:00:29 -0800 (PST), "Marshall.Harris"
<marshall.d.harris(a)gmail.com> wrote:

>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 stefi(a)discussions.microsoft.com for the macro by the way.
>
>-Marshall
>
>On Dec 16, 2:41�pm, "Marshall.Harris" <marshall.d.har...(a)gmail.com>
>wrote:
>> 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