Confusion Of Join Method

[kal0123456789]

I'm creating an app for user to select data but they don't have to select all the data. For example, they can choose to include some columns, but not the others. One thing that is known is the data selected will have the same rows(so if the user selects A1:A5 and C2:C5, then that's the user problem already). Then I want to join all the cells by the column for each row, hopping the cells that are not selected. I've seen the method in this forum, but I forgot the link(I'll add later if I can find it), so I just paste it down below. When I'm using this code, it fails because if the data selection is not contiguous. (I'm using Excel 2010 so cannot do TextJoin method)

Dim rng As Range 'The range of selection
Dim row1 As Range, cell As Range
For Each row1 In rng.Rows
    For Each cell In row1
        Vlu = Join(Application.Index(cell.Resize(row1.row).value, 1, 0), "|")
    Next cell
Next row1

My analysis of the code is because I don't understand what is the cell variable consist of in every loop, since it's not iterated from row1.Cells which can yield the cell's value by calling cell.value. Could you tell me what is inside the cell variable? And how do I apply with the selection method proposed by this link's answer?

 

[Sulprobil]

I suggest you use the user defined function TEXTJOIN shown below. Good thing is: When you migrate to Excel 2019 or newer you do not need to do anything. Excel will automatically use the built-in function. Let me know of any issues or incompatibilities, please.

Press ALT + F11, insert a new module, copy below code in there, and go back to your Excel sheet and use it:

Option Explicit
Function TEXTJOIN(Delimiter As String, _
    Ignore_empty As Boolean, _
    ParamArray Text() As Variant) As String
Dim v, i As Long, s As String
For i = LBound(Text) To UBound(Text)
    For Each v In Text(i)
        If Not (Ignore_empty And v = "") Then
            TEXTJOIN = TEXTJOIN & s & v
            s = Delimiter
        End If
    Next v
Next i
End Function

 

[kal0123456789]

I suggest you use the user defined function TEXTJOIN shown below. Good thing is: When you migrate to Excel 2019 or newer you do not need to do anything. Excel will automatically use the built-in function. Let me know of any issues or incompatibilities, please.

Press ALT + F11, insert a new module, copy below code in there, and go back to your Excel sheet and use it:

Option Explicit
Function TEXTJOIN(Delimiter As String, _
    Ignore_empty As Boolean, _
    ParamArray Text() As Variant) As String
Dim v, i As Long, s As String
For i = LBound(Text) To UBound(Text)
    For Each v In Text(i)
        If Not (Ignore_empty And v = "") Then
            TEXTJOIN = TEXTJOIN & s & v
            s = Delimiter
        End If
    Next v
Next i
End Function

Is there a way to convert the range like itRng in this link to be Text()? It seems I can't convert it, either by Text = itRng, whether the declaration is using Text As Variant or Text() As Variant, and either itRng or itRng.Value.

 

[BSALV]

Sub Test()
     'some examples !!!!!
     MsgBox MyTextJoin(Range("A1:A5,A10:A20"))
     MsgBox MyTextJoin(Range("A1:C1,F1:H1"))
     MsgBox MyTextJoin(Range("A1:C1,F20:H20"))
     MsgBox MyTextJoin(Range("A1:A5,F20:F100"))
     
End Sub

Function MyTextJoin(c)
     '*******************************************************************
     'this UDF joins the content of multiple ranges, as Long as they are all in 1 row or 1 column
     '*******************************************************************
     
    mySep = "|" 'this is the wanted separator
    
     If Application.Min(Intersect(c.Cells(1).EntireColumn, c.EntireRow).Areas.Count, Intersect(c.Cells(1).EntireRow, c.EntireColumn).Areas.Count) <> 1 Then
     'MsgBox "error range, not entirely in 1 row or column", vbCritical
          MyTextJoin = "error": Exit Function
     End If

     For Each ar In c.Areas
          If ar.Rows.Count = 1 Then 'in case everything in 1 row
               MyTextJoin = MyTextJoin & mySep & Join(Application.Transpose(Application.Transpose(ar.Value)), mySep)
          ElseIf ar.Columns.Count = 1 Then 'in case everything in 1 column
               arr = Application.Transpose(ar.Value)
               MyTextJoin = MyTextJoin & mySep & Join(Application.Transpose(ar.Value), mySep)
          End If
     Next
     If Len(MyTextJoin) > 1 Then MyTextJoin = Mid(MyTextJoin, 2)     'delete leading mySep
End Function

 

[Sulprobil]

Is there a way to convert the range like itRng in this link to be Text()? It seems I can't convert it, either by Text = itRng, whether the declaration is using Text As Variant or Text() As Variant, and either itRng or itRng.Value.

That's what my UDF does, I think. An example (I have renamed my function to TEXTJOIN2019 - you can see that Excel's 2021 TEXTJOIN can't even handle this):

TextVerketten.xlsm
	A	B	C	D	E	F	G	H	I	J
1	1|1	1|2	1|3	1|4	1|5	1|6	1|7	1|8	1|9	1|10
2	2|1	2|2	2|3	2|4	2|5	2|6	2|7	2|8	2|9	2|10
3	3|1	3|2	3|3	3|4	3|5	3|6	3|7	3|8	3|9	3|10
4	4|1	4|2	4|3	4|4	4|5	4|6	4|7	4|8	4|9	4|10
5	5|1	5|2	5|3	5|4	5|5	5|6	5|7	5|8	5|9	5|10
6	6|1	6|2	6|3	6|4	6|5	6|6	6|7	6|8	6|9	6|10
7	7|1	7|2	7|3	7|4	7|5	7|6	7|7	7|8	7|9	7|10
8	8|1	8|2	8|3	8|4	8|5	8|6	8|7	8|8	8|9	8|10
9	9|1	9|2	9|3	9|4	9|5	9|6	9|7	9|8	9|9	9|10
10	10|1	10|2	10|3	10|4	10|5	10|6	10|7	10|8	10|9	10|10
11
12
13
14	TEXTJOIN2019	1|1,1|2,2|1,2|2,3|3,4|4,5|5,5|6,6|5,6|6,7|7,7|8,8|7,8|8,9|9
15	TEXTJOIN	#WERT!
MrExcel 20220105 0552

Cell Formulas
Range	Formula
A1:J10	A1	=ROW() & "|" & COLUMN()
B14	B14	=TEXTJOIN2019(",",FALSE,ThreeByThreeByThree TwoByTwoByFive)
B15	B15	=TEXTJOIN(",",FALSE,ThreeByThreeByThree TwoByTwoByFive)

 

[Fluff]

@Sulprobil it would help if you included information as to what ThreeByThreeByThree & TwoByTwoByFive are meant to be.

 

[Sulprobil]

Hi Fluff,
Apologies, I thought that XL2BB would have shown that:
Named Ranges:
ThreeByThreebyThree ='MrExcel 20220105 0552'!$A$1:$C$3;'MrExcel 20220105 0552'!$D$4:$F$6;'MrExcel 20220105 0552'!$G$7:$I$9
TwoByTwoByFive ='MrExcel 20220105 0552'!$A$1:$B$2;'MrExcel 20220105 0552'!$C$3:$D$4;'MrExcel 20220105 0552'!$E$5:$F$6;'MrExcel 20220105 0552'!$G$7:$H$8;'MrExcel 20220105 0552'!$I$9:$J$10
In the English version you would need to use "," as separator, I presume.
Regards,
Bernd

 

[Fluff]

Thanks for that.

I thought that XL2BB would have shown that:

It should do if you selected that option.

 

[Sulprobil]

Well, it didn't:

XL2BB v2.0 bug: Named ranges not listed in Capture Range

Hello, Named ranges were missing in my XL2BB capture range, Mini Sheet - XL2BB v2.0 bug: Named ranges not listed in Capture Range, Mini Sheet of German Excel Version of Microsoft Office LTSC Professional Plus 2021, Version 2108 (Build 14332.20204). See thread Confusion Of Join Method, please...

www.mrexcel.com

My example without named ranges:

TextVerketten.xlsm
	A	B	C	D	E	F	G	H	I	J
1	1|1	1|2	1|3	1|4	1|5	1|6	1|7	1|8	1|9	1|10
2	2|1	2|2	2|3	2|4	2|5	2|6	2|7	2|8	2|9	2|10
3	3|1	3|2	3|3	3|4	3|5	3|6	3|7	3|8	3|9	3|10
4	4|1	4|2	4|3	4|4	4|5	4|6	4|7	4|8	4|9	4|10
5	5|1	5|2	5|3	5|4	5|5	5|6	5|7	5|8	5|9	5|10
6	6|1	6|2	6|3	6|4	6|5	6|6	6|7	6|8	6|9	6|10
7	7|1	7|2	7|3	7|4	7|5	7|6	7|7	7|8	7|9	7|10
8	8|1	8|2	8|3	8|4	8|5	8|6	8|7	8|8	8|9	8|10
9	9|1	9|2	9|3	9|4	9|5	9|6	9|7	9|8	9|9	9|10
10	10|1	10|2	10|3	10|4	10|5	10|6	10|7	10|8	10|9	10|10
11
12
13
14	TEXTJOIN2019	1|1,1|2,2|1,2|2,3|3,4|4,5|5,5|6,6|5,6|6,7|7,7|8,8|7,8|8,9|9
15	TEXTJOIN	#WERT!
Tabelle3

Cell Formulas
Range	Formula
A1:J10	A1	=ROW() & "|" & COLUMN()
B14	B14	=TEXTJOIN2019(",",FALSE,(A1:C3,D4:F6,G7:I9) (A1:B2,C3:D4,E5:F6,G7:H8,I9:J10))
B15	B15	=TEXTJOIN(",",FALSE,(A1:C3,D4:F6,G7:I9) (A1:B2,C3:D4,E5:F6,G7:H8,I9:J10))

But, no worries,
Regards,
Bernd

 

[kal0123456789]

@Sulprobil, your method does work. Thank you very much!
@BSALV, your method does work also, but it seems I have to loop to intersect range which is troublesome(although at the end find a way to do it for another purpose).
For now, I'll proceed with @Sulprobil's method. Thank you for answering my question!