Textjoin question

[Holley]

I have tweaked Fluff's code to use Textjoin a column in vba. Everything works beautifully, except I a getting a "," when there are blank cells. If there is no data, I need for the cells to remain blank.

Sub textjoin()
   With Range("AI2:AI" & Range("A" & Rows.Count).End(xlUp).Row)
   .Formula = "=TEXTJOIN("","",TRUE,AE2:AH2)"
      .Value = .Value
   End With
  End Sub

When I use the formula that provides the desired results directly in the spreadsheet, I get a Run-Time 1004 error

Sub textjoin()
   With Range("AI2:AI" & Range("A" & Rows.Count).End(xlUp).Row)
   .Formula = "=TEXTJOIN("",TRUE,AE2:AH2)"
      .Value = .Value
   End With
  End Sub

Any suggestions on preventing this would be most appreciated!!

 

[Rick Rothstein]

I am not 100% sure of why you have TRUE in there, but see if changing this line...

.Formula = "=TEXTJOIN("",TRUE,AE2:AH2)"

to this...

.Formula = "=TEXTJOIN("""","""",,AE2:AH2)"

does what you want.

 

[Holley]

Thanks for your quick reply! I was thinking the TRUE was to skip the empty cells. I did try this, but I received a "VALUE! error on each of the rows

Sub textjoin()
   With Range("AI2:AI" & Range("A" & Rows.Count).End(xlUp).Row)
   .Formula = "=TEXTJOIN("""","""",AE2:AH2)"
      .Value = .Value
   End With
  End Sub

 

[Alex Blakenburg]

Try either of these:

Sub textjoin()
   With Range("AI2:AI" & Range("A" & Rows.Count).End(xlUp).Row)
   .Formula = "=TEXTJOIN("""",,AE2:AH2)"            ' Nothing in the True/False position
   ' OR
   ' .Formula = "=TEXTJOIN("""",TRUE,AE2:AH2)"  ' Alternative
      .Value = .Value
   End With
  End Sub

 

[Holley]

thanks for all the feedback! I kept working with it and was able to get it to work using the below code: I only needed to add a space between the two quotations.

Sub textjoin()
   With Range("AI2:AI" & Range("A" & Rows.Count).End(xlUp).Row)
   .Formula = "=TEXTJOIN("" "",True,AE2:AH2)"
   .Value = .Value
   End With
  End Sub

I do appreciate all of the help!!

 

[Alex Blakenburg]

You're welcome. Glad we could help.