Macro to concatenate previously selected cells

Hello, everyone!

I'm trying to write a macro that would allow me to concatenate the
contents of several already selected (but not adjacent) cells into one
cell. In other words, I want to be able to go to a worksheet, select
several different cells by using the Ctrl key and then run the macro to
concatenate what is in those cells into one cell, preferably the upper
leftmost cell of the ones selected.

Any help on this would be greatly appreciate.

My first post, so don't chastise me too much if what I'm asking has a
simple solution.

Thanks,

How about:


Sub Macro1()
Dim r As Range
Dim rr As Range
Dim i As Integer
Dim t As String
i = 0
t = ""
For Each r In Selection
If i = 0 Then
i = 1
Set rr = r
End If
t = t & r.Value
Next
rr.Value = t
End Sub

I only tried it once.
--
Gary's Student


"Grumpy" wrote:

> Hello, everyone!
>
> I'm trying to write a macro that would allow me to concatenate the
> contents of several already selected (but not adjacent) cells into one
> cell. In other words, I want to be able to go to a worksheet, select
> several different cells by using the Ctrl key and then run the macro to
> concatenate what is in those cells into one cell, preferably the upper
> leftmost cell of the ones selected.
>
> Any help on this would be greatly appreciate.
>
> My first post, so don't chastise me too much if what I'm asking has a
> simple solution.
>
> Thanks,
>
>

This works! I altered your code slightly (to give spaces into the final
result):

Sub ConcatenateRange()
Dim celCurrentCell As Range
Dim celFirstCell As Range
Dim i As Integer
Dim txtTempText As String
i = 0
txtTempText = ""
For Each celCurrentCell In Selection
If i = 0 Then
i = 1
Set celFirstCell = celCurrentCell
End If
txtTempText = txtTempText & celCurrentCell.Value & " "
Next
txtTempText = Left(txtTempText, Len(txtTempText) - 1)
celFirstCell.Value = txtTempText
End Sub