连接未知数量的列和行 [英] concatenate unknown number of column and rows

问题描述

几天以来，我一直在尝试某些东西，但我真的迷失了。

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

我想将Excel中的列从第一列连接到最后一个非空列，并在每列之间添加逗号。

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

推荐答案

code> TEXTJOIN 用于不具备此功能的版本（Excel 2013及更低版本）：

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

^{（ 来源 ）}

示例：

如果您想使用逗号作为分隔符来连接A列中每个填充的单元格，请使用：

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

=TEXTJOIN(",",TRUE,A:A)

这篇关于连接未知数量的列和行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！