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

Set rCells = Selection

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

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"(E-Mail Removed)" wrote:

> 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
>
>

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)

<(E-Mail Removed)> wrote in message
news:6334143c-3bf0-40d8-a853-(E-Mail Removed)...
> 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
>