Confusion Of Join Method

kal0123456789

New Member
Joined
Jan 4, 2022
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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)

VBA Code:
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
194
Office Version
  1. 2021
Platform
  1. Windows
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:

VBA Code:
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

New Member
Joined
Jan 4, 2022
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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:

VBA Code:
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

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
VBA Code:
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

Board Regular
Joined
May 12, 2020
Messages
194
Office Version
  1. 2021
Platform
  1. Windows
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):

Cell Formulas
RangeFormula
A1:J10A1=ROW() & "|" & COLUMN()
B14B14=TEXTJOIN2019(",",FALSE,ThreeByThreeByThree TwoByTwoByFive)
B15B15=TEXTJOIN(",",FALSE,ThreeByThreeByThree TwoByTwoByFive)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,501
Office Version
  1. 365
Platform
  1. Windows
@Sulprobil it would help if you included information as to what ThreeByThreeByThree & TwoByTwoByFive are meant to be. ;)
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
194
Office Version
  1. 2021
Platform
  1. Windows
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
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
194
Office Version
  1. 2021
Platform
  1. Windows
Well, it didn't:

My example without named ranges:
Cell Formulas
RangeFormula
A1:J10A1=ROW() & "|" & COLUMN()
B14B14=TEXTJOIN2019(",",FALSE,(A1:C3,D4:F6,G7:I9) (A1:B2,C3:D4,E5:F6,G7:H8,I9:J10))
B15B15=TEXTJOIN(",",FALSE,(A1:C3,D4:F6,G7:I9) (A1:B2,C3:D4,E5:F6,G7:H8,I9:J10))


But, no worries,
Regards,
Bernd
 
Solution

kal0123456789

New Member
Joined
Jan 4, 2022
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
@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!
 

Forum statistics

Threads
1,175,502
Messages
5,897,800
Members
434,677
Latest member
Aurelied

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top