Concatenate Problem

[JayLee]

Is there code in order to get the concatenated results which are in column c?

Book1
	A	B	C	D	E
1	Description	StockTicker
2	JMSsystems	JMS	JMSsystemsboughtat100.5
3	boughtat
4	100.5
5	TheFarSide	FSA	TheFarSideoftheroad
6	oftheroad
Sheet1

 

[Drdave1958]

Here's one way...
=A4 &" "& A5&" " &A6

Dave

 

[Drdave1958]

This seems to work also...
=CONCATENATE(A4, " ", A5, " ", A6)

Dave

 

[JayLee]

I have code that did concatenated the results into a column only if there were spaces between the description. Now the data is changed and there are no longer any more spaces. Here is the code:

Sub Concatenate()
'Concatenate Rows
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("D3:D" & Range("D65536").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, 4) = 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
Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("D2").Select
ActiveSheet.Paste

End Sub

Now i want to modify this code to concatenate to the previous row if a cell in column B is empty.

 

[JayLee]

Can anyone help me with this?

 

[attc]

This slightly modified code should do the trick (based on the data as per your example):

Sub Concatenate()
'Concatenate Rows
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("A2: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, 1)) And x + y <= Rng.Rows.Count Then

Txt = Txt & " " & Rng.Cells(x, 1).Offset(y, 0)
y = y + 1
Debug.Print "1 " & Txt

Else

Rng.Cells(x, 1).Offset(0, 2) = Txt
x = x + y
y = 1
Exit Do

End If

Loop While x + y <= Rng.Rows.Count + 1
Loop While x <= Rng.Rows.Count

End Sub

 

[JayLee]

Thanks. It works fine!