Is there away to create a macro to put numbers in one row with comma
between them instead having them in 3000 rows?
From
A
1 5656565656
2 5353535353
3 6262626262
4 3535353535
5 2326235623

To
A
1 "5656565656","5353535353","6262626262","3535353535","2326235623"

the last column is IV. there is a limit of 230 columns. the number of rows
can be in the 1000. You will not get all your data in 1 row.

"MarkyMark" wrote:

> Is there away to create a macro to put numbers in one row with comma
> between them instead having them in 3000 rows?
> From
> A
> 1 5656565656
> 2 5353535353
> 3 6262626262
> 4 3535353535
> 5 2326235623
>
> To
> A
> 1 "5656565656","5353535353","6262626262","3535353535","2326235623"
>
>

IV is column 256 and xl2007 extended that to 16384 columns (XFD).

And there's 64k of rows in xl97-xl2003. 1Meg in xl2007.

But I agree with your question....

10 characters surrounded by a pair of double quotes and separated by a comma
would mean that:

3000 * (10+2+1) Characters in that one giant cell.
3000 * 13
39000

And excel can hold about 32k characters per cell.

Maybe the question becomes: What does the OP really want?

Joel wrote:
>
> the last column is IV. there is a limit of 230 columns. the number of rows
> can be in the 1000. You will not get all your data in 1 row.
>
> "MarkyMark" wrote:
>
> > Is there away to create a macro to put numbers in one row with comma
> > between them instead having them in 3000 rows?
> > From
> > A
> > 1 5656565656
> > 2 5353535353
> > 3 6262626262
> > 4 3535353535
> > 5 2326235623
> >
> > To
> > A
> > 1 "5656565656","5353535353","6262626262","3535353535","2326235623"
> >
> >

--

Dave Peterson

10 * 3000 = 30000 + 2999 commas + 6000 double quotes would make more
characters than a cell can hold.

Sub CCC()
Dim cell As Range, s As String
For Each cell In Range("A1:A3000")
s = s & """" & cell.Value & ""","
Next
Range("B9").Value = Left(s, Len(s) - 1)


End Sub

would be the basic approach

--
Regards,
Tom Ogilvy


"MarkyMark" wrote:

> Is there away to create a macro to put numbers in one row with comma
> between them instead having them in 3000 rows?
> From
> A
> 1 5656565656
> 2 5353535353
> 3 6262626262
> 4 3535353535
> 5 2326235623
>
> To
> A
> 1 "5656565656","5353535353","6262626262","3535353535","2326235623"
>
>

As Dave pointed out, getting 10 lbs into a 5 lb bag is difficult.
If you can live with multiple bags then (data is in B5:B9)...

Sub InsertComma()
Dim vArr As Variant
vArr = Join(Application.Transpose(Range("B5:B9")), ",")
Range("C5").NumberFormat = "@"
Range("C5").Value = vArr
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"MarkyMark" <(E-Mail Removed)>
wrote in message
Is there away to create a macro to put numbers in one row with comma
between them instead having them in 3000 rows?
From
A
1 5656565656
2 5353535353
3 6262626262
4 3535353535
5 2326235623

To
A
1 "5656565656","5353535353","6262626262","3535353535","2326235623"

Yes, but with 3000 10-digit numbers including commas, you would exceed Excel's
32.767 character limit per cell.

You also would not be able to see or print more than about 100 of them due to
Excel's 1024 limit on visible characters in a cell.

Still want to do this?


Gord Dibben MS Excel MVP

On 21 Feb 2007 10:19:26 -0800, "MarkyMark" <(E-Mail Removed)> wrote:

>Is there away to create a macro to put numbers in one row with comma
>between them instead having them in 3000 rows?
>From
> A
>1 5656565656
>2 5353535353
>3 6262626262
>4 3535353535
>5 2326235623
>
>To
> A
>1 "5656565656","5353535353","6262626262","3535353535","2326235623"

Thank you all i have 2007 office

One cell in 2007 is pretty much the same as one cell in earlier versions. to
the best of my knowledge, the limit is still 32K.

Nonetheless, I gave you a macro to try it.

--
Regards,
Tom Ogilvy


"MarkyMark" wrote:

> Thank you all i have 2007 office
>
>