Hi,

I had create a NameRange from Insert > Define > Name

That is fro say from a1:a5

Now I want to copy all a1:a5 in cell B1.

Can any one halp me?

Thanks in advance
--
Dhimant

Are you saying that if a1:a5 has a,b,c,d then you want cell b1 to say
a,b,c,d in one cell? Spaces?, Be SPECIFIC.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Dhimant" <(E-Mail Removed)> wrote in message
news:E91BF00B-335B-440E-8417-(E-Mail Removed)...
> Hi,
>
> I had create a NameRange from Insert > Define > Name
>
> That is fro say from a1:a5
>
> Now I want to copy all a1:a5 in cell B1.
>
> Can any one halp me?
>
> Thanks in advance
> --
> Dhimant

yes it should be like a,b,c,d
--
India


"Don Guillett" wrote:

> Are you saying that if a1:a5 has a,b,c,d then you want cell b1 to say
> a,b,c,d in one cell? Spaces?, Be SPECIFIC.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Dhimant" <(E-Mail Removed)> wrote in message
> news:E91BF00B-335B-440E-8417-(E-Mail Removed)...
> > Hi,
> >
> > I had create a NameRange from Insert > Define > Name
> >
> > That is fro say from a1:a5
> >
> > Now I want to copy all a1:a5 in cell B1.
> >
> > Can any one halp me?
> >
> > Thanks in advance
> > --
> > Dhimant
>
>

If your data reside in only one column and the name of the range is fro,
try this one. but this one doesn't work if your data reside in multiple
columns.

Sub test()
Dim arr
arr = Application.Transpose(Range("fro"))
Range("fro").Offset(0, 1).Resize(1, 1) = join(arr, ",")
End Sub

keiji

Dhimant wrote:
> Hi,
>
> I had create a NameRange from Insert > Define > Name
>
> That is fro say from a1:a5
>
> Now I want to copy all a1:a5 in cell B1.
>
> Can any one halp me?
>
> Thanks in advance

Dear Keiji,

its working....But will it possible to convert it into functin?
i mean if i write =abc(arr) and the reasult will be a,b,c,d,e
--
Dhimant


"keiji kounoike" <"kounoike AT mbh.nifty." wrote:

> If your data reside in only one column and the name of the range is fro,
> try this one. but this one doesn't work if your data reside in multiple
> columns.
>
> Sub test()
> Dim arr
> arr = Application.Transpose(Range("fro"))
> Range("fro").Offset(0, 1).Resize(1, 1) = join(arr, ",")
> End Sub
>
> keiji
>
> Dhimant wrote:
> > Hi,
> >
> > I had create a NameRange from Insert > Define > Name
> >
> > That is fro say from a1:a5
> >
> > Now I want to copy all a1:a5 in cell B1.
> >
> > Can any one halp me?
> >
> > Thanks in advance
>

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=concatrange(fro)

Or just =concatrange(a1:a5)


Gord Dibben MS Excel MVP

On Sun, 16 Nov 2008 03:26:04 -0800, Dhimant
<(E-Mail Removed)> wrote:

>Dear Keiji,
>
>its working....But will it possible to convert it into functin?
>i mean if i write =abc(arr) and the reasult will be a,b,c,d,e