Concatenate Question

[JayLee]

Is there vb code that would allow me to do this? This would help me so much! Thanks!

Book1
	A	B	C	D
1
2		ThisistheresultIwantinthiscolumn
3	TheDogJumped	TheDogJumpedoverthefencenearmyhouse.
4	overthefence
5	nearmyhouse.
6
7	TheCatran	TheCatranAround.
8	Around.
9
10	Weekthree	Weekthreeisthetimeforparty.
11	isthetime
12	forparty.
Sheet1

 

[Andrew Poulsom]

This seems to work:

Sub Test()
    Dim Rng As Range
    Dim x As Long
    Dim y As Integer
    Dim Txt As String
'   *** Change column and starting row to suit ***
    Set Rng = Range("A3:A" & Range("A65536").End(xlUp).Row)
    x = 1
    y = 1
    Do
        Txt = Rng.Cells(x, 1)
        Do
            If IsEmpty(Rng.Cells(x, 1).Offset(y, 0)) Then
                Rng.Cells(x, 1).Offset(0, 1) = Txt
                x = x + y + 1
                y = 1
                Exit Do
            Else
                Txt = Txt & Rng.Cells(x, 1).Offset(y, 0)
                y = y + 1
            End If
        Loop
    Loop While x <= Rng.Rows.Count
End Sub

Hope you don't mind not having formulas.

 

[WillR]

i believe you can just call a worksheet function in your VB code like this....

WorksheetFunction.Concatenate(A1,A2,A3)

HTH

 

[Andrew Poulsom]

WillR,

I know but I was too lazy to go to the trouble of building the formula. I think the OP used CONCATENATE just to show the required results.

 

[JayLee]

Yep, I just did that to show the results. Thanks for all your help!