excel - 多列合并为一列，以逗号分隔

Question

我有超过 2k 行的文件，数据如下:

我想将其更改为一列的行，其中数据用逗号分隔。

我找到了可以完成这项工作的 vba 代码，但我必须分别选择每一行和列，并且它将空白单元格显示为一堆逗号，我不会显示这些逗号。

我的代码:

Sub Columns_to_rows()
'
' Columns to rows Makro
'
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
    If outStr = "" Then
        outStr = rng.Value
    Else
        outStr = outStr & "," & rng.Value
    End If
Next
OutRng.Value = outStr
End Sub

Answer 1

使用现有代码作为起点，您可以使用 TEXTJOIN 函数公式来构建回复，然后删除公式，只留下响应:

Sub Columns_to_rows()
    '
    ' Columns to rows Macro
    '
    Dim rng As Range, ofst As Long
    Dim InputRng As Range, OutRng As Range
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
    Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
    
    ofst = 0
    
    For Each rng In InputRng.Rows
        OutRng.Offset(ofst).Formula = "=TextJoin("","", True, " & rng.Address & ")"
        ofst = ofst + 1
    Next
    OutRng.Resize(ofst).Value = OutRng.Resize(ofst).Value
    
End Sub