时间戳

首页 > 解决方案 > VBA在循环中组合列堆栈

vba - VBA在循环中组合列堆栈

问题描述

我有在循环中堆叠的问题宏应该将所有列（可变的行数）组合成一列。

Sub CombineColumns()
Dim xRng As Range
Dim i As Integer
Dim xLastRow As Integer
On Error Resume Next
Set xRng = Application.Range("A1", Range("A1").End(xlDown).End(xlToRight))
xLastRow = xRng.Columns(1).Rows.Count + 1

For i = 2 To xRng.Columns.Count
    Range(xRng.Cells(1, i), xRng.Cells(xRng.Columns(i).Rows.Count, i)).Cut
    ActiveSheet.Paste Destination:=xRng.Cells(xLastRow, 1)
    xLastRow = xLastRow + xRng.Columns(i).Rows.Count
Next

结束子

标签： vbamultiple-columns

解决方案

使用 Array 既简单又快速。

Sub test()
    Dim Ws As Worksheet, toWS As Worksheet
    Dim vDB, vR()
    Dim i As Long, j As Integer, n As Long
    Set Ws = ActiveSheet
    vDB = Ws.UsedRange

    r = UBound(vDB, 1)
    c = UBound(vDB, 2)

    For i = 1 To r
        For j = 1 To c
            n = n + 1
            ReDim Preserve vR(1 To n)
            vR(n) = vDB(i, j)
        Next j
    Next i

    Set toWS = Sheets.Add ' set toWs = Sheets(2)  ~~> set your sheet
    With toWS
        .Cells.Clear
        .Range("a1").Resize(n) = WorksheetFunction.Transpose(vR)
    End With
End Sub

推荐阅读