excel - 在excel vba中将多列堆叠成一列

Question

我有多列（15 到 16）的数据（见图片）。

它有一个数字，分为 3 列，例如。147被分成三列1、4、7和268被分成2、6、8。现在我想以如图所示的方式堆叠数据

为此，我尝试连接三列以生成单个数字，例如 1、4、7 组合成 147，2、6、8 组合成 268。我编写的代码给出了输出 148 和 268，但是它在它们之间有两个空列，如下所示。

我无法堆叠这些列以获得所需的输出。请建议任何直接从输入堆叠到所需输出的方法，或者对我当前的代码进行任何修改，以便我在连续列中获得串联数据。

注意：- 行数和列数是可变的而不是静态的。

Sub JoinAndCut()
Dim n As Long
Dim p, col As Long
Dim lastrow As Long
For p = 1 To 25 Step 3

lastrow = ThisWorkbook.Worksheets(2).Cells(Rows.count, p).End(xlUp).Row   '<== To Count number of rows in each column

For n = 2 To lastrow

Cells(n, p).Offset(, 25).Value = Cells(n, p).Value & Cells(n, p + 1).Value & Cells(n, p + 2).Value ' <=== Offset by 25 values so as they dont overlap the input

Next n
Next p

End Sub


Sub JoinAndCut()
Dim n As Long
Dim p, col As Long
Dim lastrow As Long
For p = 1 To 25 Step 3

lastrow = ThisWorkbook.Worksheets(2).Cells(Rows.count, p).End(xlUp).Row   '<== To Count number of rows in each column

For n = 2 To lastrow

Cells(n, p).Offset(, 25).Value = Cells(n, p).Value & Cells(n, p + 1).Value & Cells(n, p + 2).Value ' <=== Offset by 25 values so as they dont overlap the input

Next n
Next p

End Sub

标签： excelvba

Answer 1

你可以试试这个：

Sub JoinAndCut()
    Dim n As Long
    Dim p, col As Long
    Dim lastrow As Long, lastrowstack As Long
    For p = 1 To 25 Step 3

        lastrow = ThisWorkbook.Worksheets(2).Cells(Rows.Count, p).End(xlUp).Row   '<== To get row number in each column
        lastrowstack = ThisWorkbook.Worksheets(2).Cells(Rows.Count, 26).End(xlUp).Row   '<== To get row number in in the stacked column

        For n = 2 To lastrow
            
            Cells(n + lastrowstack - 1, 26).Value = Cells(n, p).Value & Cells(n, p + 1).Value & Cells(n, p + 2).Value ' <=== Starting in column 26 so as they dont overlap the input

        Next n
    Next p

End Sub

假设你有这样的事情：

html格式的数据

然后将它们组合并堆叠在 Z 列（又名第 26 列）中，前 36 行将如下所示。