Bob Phillips
12/20/2006 12:11:00 PM
Dylan,
My error sorry, I forgot an important bit.
I have corrected that, and also added code to deal with empty cells, so you
don't get repeated commas
Sub ConcatAndMove()
Dim srcrng As Range
Dim tgtrng As Range
Dim cell As Range
Dim tmp As String
Set srcrng = Application.InputBox("Select input range with mouse",
Type:=8)
If Not srcrng Is Nothing Then
For Each cell In srcrng
If Not cell.Value = "" Then tmp = tmp & cell.Value & ","
Next cell
End If
Set tgtrng = Application.InputBox("Select TARGET range with mouse",
Type:=8)
If tmp <> "" Then tgtrng.Value = Left(tmp, Len(tmp) - 1)
End Sub
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"dd" <dd.dd> wrote in message news:uUpdMECJHHA.3668@TK2MSFTNGP02.phx.gbl...
> Bob,
>
> Thanks for your assistance. This seems to follow through with what I
> wanted,
> but the results are a bit weird. The target cell "A1" in this case
> displays
> a series of dots depending on how many source cells I select i.e. three
> cells selected then "..." appears in Sheet2.A1
>
> Regards
> Dylan Dawson
>
> Sub ConcatAndMove()
>
> Dim srcrng As Range
> Dim tgtrng As Range
> Dim cell As Range
> Dim tmp As String
>
> Set srcrng = Application.InputBox("Select range with mouse", Type:=8)
> If Not srcrng Is Nothing Then
> For Each cell In srcrng
> tmp = tmp & ","
> Next cell
> End If
> Set tgtrng = Application.InputBox("Select range with mouse", Type:=8)
> tgtrng.Value = Left(tmp, Len(tmp) - 1)
>
> End Sub
>
> "Bob Phillips" <bob.NGs@xxxx.com> wrote in message
> news:e4K9vwBJHHA.3268@TK2MSFTNGP04.phx.gbl...
> Dim rng As Range
> Dim cell as Range
> Dim tmp As string
>
> set rng = Application.InputBox("Select range with mouse",Type:=8)
> If Not rng Is Nothing Then
> For Each cell in rng
> tmp = tmep & ","
> Next cell
> End If
> Worksheets("Sheet2").Range("A1").value = _
> Left(tmp,Len(tmp) - 1)
>
> --
> ---
> HTH
>
> Bob
>
> (change the xxxx to gmail if mailing direct)
>
>
> "dd" <dd.dd> wrote in message
> news:uP2gepBJHHA.2236@TK2MSFTNGP02.phx.gbl...
>> Does anyone know how to:
>> Select a group of cells, say in a table column,
>> concatenate the contents (perhaps separated by commas)
>> and copy over to a cell selected in Sheet2?
>>
>> I tried to record a macro to do this then install a RefEdit into the
>> macro,
>> but I am a total bottom-eater at excel VBA.
>>
>> Regards
>> Dylan Dawson
>> Scotland
>>
>>
>
>
>