VB code to concatenate 100+ cells

[LJones]

Hello,

Very amateur VB user, need help!

I need to write VB code to concatenate the data in 100 or more cells into a
single string. This is easy for a small number of cells:

ActiveCell.Offset(0, 0).FormulaR1C1 = _
ActiveCell.Offset(0, -1) & "," & ActiveCell.Offset(0, -2) etc.

But how do I do this without needing to type the "ActiveCell.Offset" 100
times? Is there any way to included a for/next loop within the concatenation?

Thanks!
/LJ

[Toppers]

Hi,


Dim myString as string

myString=""

For r=1 to 100
myString=MyString & cells(r,1) <=== Rows 1 to 100 of column A
next r


"LJones" wrote:

> Hello,
>
> Very amateur VB user, need help!
>
> I need to write VB code to concatenate the data in 100 or more cells into a
> single string. This is easy for a small number of cells:
>
> ActiveCell.Offset(0, 0).FormulaR1C1 = _
> ActiveCell.Offset(0, -1) & "," & ActiveCell.Offset(0, -2) etc.
>
> But how do I do this without needing to type the "ActiveCell.Offset" 100
> times? Is there any way to included a for/next loop within the concatenation?
>
> Thanks!
> /LJ

[Toppers]

Soory ... forgot about comma:

Dim myString as string

myString=""

For r=1 to 100
myString=MyString & cells(r,1) & "," <=== Rows 1 to 100 of column A
next r

myString=left(myString,len(mystring)-1)


"Toppers" wrote:

> Hi,
>
>
> Dim myString as string
>
> myString=""
>
> For r=1 to 100
> myString=MyString & cells(r,1) <=== Rows 1 to 100 of column A
> next r
>
>
> "LJones" wrote:
>
> > Hello,
> >
> > Very amateur VB user, need help!
> >
> > I need to write VB code to concatenate the data in 100 or more cells into a
> > single string. This is easy for a small number of cells:
> >
> > ActiveCell.Offset(0, 0).FormulaR1C1 = _
> > ActiveCell.Offset(0, -1) & "," & ActiveCell.Offset(0, -2) etc.
> >
> > But how do I do this without needing to type the "ActiveCell.Offset" 100
> > times? Is there any way to included a for/next loop within the concatenation?
> >
> > Thanks!
> > /LJ

[LJones]

Perfect! Thx again,
LJ

"Toppers" wrote:

> Soory ... forgot about comma:
>
> Dim myString as string
>
> myString=""
>
> For r=1 to 100
> myString=MyString & cells(r,1) & "," <=== Rows 1 to 100 of column A
> next r
>
> myString=left(myString,len(mystring)-1)
>
>
> "Toppers" wrote:
>
> > Hi,
> >
> >
> > Dim myString as string
> >
> > myString=""
> >
> > For r=1 to 100
> > myString=MyString & cells(r,1) <=== Rows 1 to 100 of column A
> > next r
> >
> >
> > "LJones" wrote:
> >
> > > Hello,
> > >
> > > Very amateur VB user, need help!
> > >
> > > I need to write VB code to concatenate the data in 100 or more cells into a
> > > single string. This is easy for a small number of cells:
> > >
> > > ActiveCell.Offset(0, 0).FormulaR1C1 = _
> > > ActiveCell.Offset(0, -1) & "," & ActiveCell.Offset(0, -2) etc.
> > >
> > > But how do I do this without needing to type the "ActiveCell.Offset" 100
> > > times? Is there any way to included a for/next loop within the concatenation?
> > >
> > > Thanks!
> > > /LJ