combine cells into one.

[erodjob]

I have cells 1:6970 with data in each cell. How can I combine all values in cells 1:6970 into cell B1?

I use excel 2003.

Thank you

 

[Zack Barresse]

Hmm, why won't it work? Did you try it already??

 

[erodjob]

I have this code already and I use it within an access application referencing a table and it sends out email on by one. I want a mass email for this project. I need something like "=CONCATENATE(A1:A6970) I dont mind A1:A100 either but it does not seem to work unless i use "=CONCATENATE(A1,A2,A3,A4,A5,A6,A7)" etc. Any thoughts?

 

[Greg Truby]

How 'bout ol' Harlan's ACONCAT:

Function AConcat(varA As Variant, Optional strSep As String = "") As String
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' by Harlan Grove, March 2002
    Dim varY As Variant
    If TypeOf varA Is Range Then
        For Each varY In varA.Cells
            AConcat = AConcat & varY.Value & strSep
        Next varY
    ElseIf IsArray(varA) Then
        For Each varY In varA
            AConcat = AConcat & varY & strSep
        Next varY
    Else
        AConcat = AConcat & varA & strSep
    End If
    AConcat = Left(AConcat, Len(AConcat) - Len(strSep))
End Function

You could then do:<ul>[*]=aconcat(A1:A100,", ")[/list]

 

[Zack Barresse]

You can't use it that way, that function only takes thirty (30) arguments. Have you tried the code???

 

[Greg Truby]

Post-sequence problem, Zack? I assume you meant erodjob and not me...'cause Harlan's'll work (leastways it did on 400 cells, which is all I tested).

 

[Zack Barresse]

Even stringing them together without a function works, but has it's limits. Granted it's freakin huge limit (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vagrpDataType.asp) but it still has a limit. Personally I think Harlan's code would be quite a bit over the edge as we know exactly what we're dealing with and don't need such a large "blanket".

So that would give two distinctly possible methods right off the bat, 1) loop through the range assigning values to a variable then use the variable to populate the To box, or 2) loop through the range appending the To box itself with each value.

 

[Greg Truby]

np, Zack. It just sounded like erod already had some code and just needed the concat bit to get him over the hump. Since I already had Harlan's code in my bag of tricks, figured just *bam* pop it in dere and we're off to the races.

 

[Zack Barresse]

Gotcha.