Combine Cell Strings

[JonRowland]

The following code combines strings in Cols C&D per row.

 With Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
        .NumberFormat = "@"
        .Value = Evaluate(.Offset(0, 0).Address & "&"",""&" & .Offset(, 1).Address)
    End With

I am looking to expand this and combine B,C &D when there is text in B. If not then just combine C&Dand thought I would alter to

    With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    
    If .Value = Empty Then
        .NumberFormat = "@"
        .Value = Evaluate(.Offset(0, 1).Address & "&"", ""&" & .Offset(, 2).Address)
    Else
        .NumberFormat = "@"
        .Value = Evaluate(.Offset(0, 0).Address & "&"", ""&" & .Offset(, 1).Address & "&"", ""&" & .Offset(, 2).Address)
End If
    End With

This should be if B is blank then comine C&D
If B is not blank then combine B,C&D.

But this ends up with Type Mismatch error. Am I missing something or is this a method that can't be used?

Thanks
Jon

 

[JonRowland]

I've just realised (silly me) I could use ISEMPTY but then I'm dealing with a range so not going to work as I had hoped and think I will revert to a loop instead.

 

[Fluff]

How about

    With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
        .NumberFormat = "@"
        .Value = Evaluate("textjoin("", "",," & .Resize(, 3).Address & ")")
    End With

 

[JonRowland]

Fluff,

How about

    With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
        .NumberFormat = "@"
        .Value = Evaluate("textjoin("", "",," & .Resize(, 3).Address & ")")
    End With

Your solution appears to combine text from every row into one column and repeats for the range. I may have not explained myself clearly.

COL B	COL C	COL D
	TEXT1	TEXT2
Text1	Text2	Text3

1st row - as B is blank/empty, I would like in B the strings in C&D eg TEXT1,TEXT2
2nd row - as B isn't empty, I I would like in B Text1,Text2,Text3

 

[Fluff]

My fault, for not testing the code, will cols C & D always have data, or could they be blank?

 

[Peter_SSs]

Based on your attempted code, could it be this?

Sub test()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "@"
    .Value = Evaluate(Replace(Replace(Replace("if(#="""","""",#&"","")&%&"",""&^", "#", .Address), "%", .Offset(, 1).Address), "^", .Offset(, 2).Address))
  End With
End Sub