VB code to concatenate 100+ cells

[Guest]

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

 

[Guest]

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

 

[Guest]

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)

 

[Guest]

Perfect! Thx again,
LJ

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)