1

I am trying something since a couple of days and I am really lost about it. Could someone help me about it please.

I would like to concatenate columns in Excel from the first column to the last non-empty column and add a comma between each column.

Following that, I would like to apply the loop from the first line to the last non-empty line.

I succeed to do it with a known number of column (I add the code after) but not when the number of column is unknown.

Range("H2").Select
ActiveCell.FormulaR1C1 = _
    "=CONCATENATE(RC[-7],"","",RC[-6],"","",RC[-5],"","",RC[-4],"","",RC[-3],"","",RC[-2])"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H" & Range("A2").End(xlDown).Row), Type:=xlFillDefault
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Florian Dubocq
  • 11
  • 2

1 Answers1

3

Here's TEXTJOIN for versions that don't have it (Excel 2013 and prior):

Option Explicit
Function TEXTJOIN(delimiter As String, ignore_empty As String, ParamArray textn() As Variant) As String
    Dim i As Long
    For i = LBound(textn) To UBound(textn) - 1
        If Len(textn(i)) = 0 Then
            If Not ignore_empty = True Then
                TEXTJOIN = TEXTJOIN & textn(i) & delimiter
            End If
        Else
            TEXTJOIN = TEXTJOIN & textn(i) & delimiter
        End If
    Next
    TEXTJOIN = TEXTJOIN & textn(UBound(textn))
End Function

(Source)

Example:

If you wanted to concatenate every populated cell in Column A, using comma as delimiter, you'd use: 

=TEXTJOIN(",",TRUE,A:A)
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Thanks. I succeeded to download the TEXTJOIN function to excel. But I admit when I type the text you put or trying with random cells it stays as text into the cell and doesn't show the final result. And if I want to put the line instead of the column, can I put 2:2 instead of A:A? – Florian Dubocq Jun 07 '18 at 12:54
  • The source page you linked does not load – Taylor Alex Raine Jun 07 '18 at 13:12
  • I have 2016 and don't appear to have TEXTJOIN – QHarr Jun 08 '18 at 05:45
  • 1
    Thanks to the code given by ashlee, I copy pasted it in the vba and now I have the funcion. It is just that when I want to apply it it stays a text in the cell (ex =TEXTJOIN (....)) and it doesn't show the final result – Florian Dubocq Jun 08 '18 at 06:24
  • @FlorianDubocq - Perhaps the cell is formatted as `Text` or maybe you're putting a space before the `=`? This would be unrelated to the VBA Function. Can you get `=1+1` working in the same cell? – ashleedawg Jun 08 '18 at 14:59
  • @QHarr That's odd, you should: *"Applies To: **Excel for Office 365** Excel for Office 365 for Mac **Excel 2016** Excel 2016 for Mac Excel Online Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones Excel Mobile"* ([Source](https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)). Although it later notes " This feature is only available if you have an Office 365 subscription." (which seems to contradict the *"Applies to"* line.) I use `TextJoin` *constantly*. – ashleedawg Jun 08 '18 at 15:06
  • @ashleedawg I also don't have MinIfs for example that is only in some 2016 versions. – QHarr Jun 08 '18 at 15:18
  • Sorry it is my bad. I've just dumbly copy pasted the textjoin formula, and it should be with ; in the bracket to separate terms and not , But I still have a problem now I have #Value! whatever format I try – Florian Dubocq Jun 08 '18 at 15:22
  • @QHarr ...but you're using Excel 2016. Have you updated Office lately? – ashleedawg Jun 09 '18 at 02:24
  • @FlorianDubocq How are you using the function, and on what data? – ashleedawg Jun 09 '18 at 02:25
  • @ashleedawg Nope. I have one of the earlier 2016 releases. – QHarr Jun 09 '18 at 05:54