VBA join row of cells if value found

[jameshurst]

I am trying to join cells in a row if a value exists in a cell in that row.
The data has been imported from a .txt file and various sub headers are split along 2, 3 or 4 columns.
The cells cant be merged as the data will only be kept from the first cell.
The only words which are always constant are "contain" and "for" in column B.
What I've tried resembles this:
If cell.Value like "contain", or "for" then join all cells from column "A" to column "H" into column "B", align them centrally and make them bold.
thanks, in advance, for any help.

<code>    Sub Joining()
    Dim N As Long, i As Long, r1 As Range, r2 As Range
 Dim z As Long
 Dim arr() As Variant
 z = 1

With Activesheet
    N = .Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To N
        If .Cells(i, "B").Value Like "Summary*" Then
            arr = .Range(.Cells(i, "A"), .Cells(i, "H")).Value
            .Cells(z, "B").Value = Join(arr, " ")
            z = z + 1
        End If
    Next i
End With</code>  End Sub

 

[HungQuoc]

I am trying to join cells in a row if a value exists in a cell in that row.
The data has been imported from a .txt file and various sub headers are split along 2, 3 or 4 columns.
The cells cant be merged as the data will only be kept from the first cell.
The only words which are always constant are "contain" and "for" in column B.
What I've tried resembles this:
If cell.Value like "contain", or "for" then join all cells from column "A" to column "H" into column "B", align them centrally and make them bold.
thanks, in advance, for any help.

<code>    Sub Joining()
    Dim N As Long, i As Long, r1 As Range, r2 As Range
 Dim z As Long
 Dim arr() As Variant
 z = 1

With Activesheet
    N = .Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To N
        If .Cells(i, "B").Value Like "Summary*" Then
            arr = .Range(.Cells(i, "A"), .Cells(i, "H")).Value
            .Cells(z, "B").Value = Join(arr, " ")
            z = z + 1
        End If
    Next i
End With</code>  End Sub

replace

<code>.Cells(z, "B").Value = Join(arr, " ")</code>

.Cells(z, "B").Value = Join(Application.Transpose(Application.Transpose(arr)), " ")