How do I set a range to the currently selected cells?

[vcgreen]

Hi

Would be very grateful for a solution to this... I need to run a macro
against a currently selected range of cells. The macro creates a new
cell based on the concatenated contents of the selected cells (which
will always be just a single set in a column) like so:

Selected cells: New cell:

.. ABC ABCDEFGHI
.. DEF
.. GHI

However I can't find a way to do this as there is no 'SelectedCells'
or 'SelectedRange' property for Worksheet object.

Thanks in anticipation

Vaughan

 

[Wigi]

Set rCells = Selection

Here I used rCells as the range variable holding the cells in the selection.

 

[Bob Phillips]

I would create a UDF like so

Function Conc(rng As Range)
Dim cell As Range
For Each cell In rng
Conc = Conc & cell.Value
Next cell
End Function


and use it in a worksheet like so

=CONC(A1:A3)

or even

=CONC((A1,B9,M7))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)