多列合并，然后再最后一列后面输出

dong130

多列合并，然后再最后一列后面输出，列非常多而且是变动的，无法一个一个用公式去合并，大神们看看用vba怎么弄，非常感谢

蓝桥玄霜

合并的例子：

493861364

Sub aa()
Columns("M:M").ClearContents

Dim ws As Worksheet
Set ws = ActiveSheet

Dim maxRows As Long
Dim maxColumns As Long

' 获取当前使用的最大行数
maxRows = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
' 获取当前使用的最大列数
maxColumns = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
Cells(1, 13) = "合并"
For i = 2 To maxRows
    St = Cells(i, 1)
        For j = 2 To maxColumns
            If Cells(i, j) = "" Then Exit For
            St = St & " " & Cells(i, j)
        Next
    Cells(i, 13) = St
Next

End Sub

493861364

gwjkkkkk

Option Explicit
Sub TEST1()
    Dim ar, br, i&, j&, r&
   
    r = Cells(Rows.Count, "A").End(xlUp).Row
    ar = Range("A1:L" & r).Value
    ReDim br(1 To UBound(ar), 0)
    br(1, 0) = "合并"
   
    For i = 2 To UBound(ar)
        For j = 1 To UBound(ar, 2)
            If Len(ar(i, j)) Then br(i, 0) = br(i, 0) & " " & ar(i, j)
        Next j
        br(i, 0) = Mid(br(i, 0), 2)
    Next i
   
    Columns("M").Clear
    [M1].Resize(UBound(br)) = br
    Beep
End Sub

gwjkkkkk

参与一下。。。

swl4561255

1. Sub test()
   
2. arr = Range("A2").CurrentRegion
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. For i = 1 To UBound(arr)
   
5.     For j = 1 To UBound(arr, 2)
   
6.         If Not d.exists(i & arr(i, 1)) Then
   
7.             d(i & arr(i, 1)) = arr(i, j)
   
8.         Else
   
9.             d(i & arr(i, 1)) = d(i & arr(i, 1)) & " " & arr(i, j)
   
10.         End If
    
11.     Next
    
12. Next
    
13. [O2].Resize(d.Count, 1) = Application.Transpose(d.items)
    
14. End Sub
    

复制代码