是不是这样?
Sub test()
Dim brr(), sht, r, i, n, k, arr
Set sht = Sheets("sheet3")
r = sht.Cells(Rows.Count, 1).End(3).Row
arr = Sheets("sheet3").[a1].CurrentRegion
ReDim brr(1 To r, 1 To 3 * UBound(arr, 2)) '重新声明数组
For i = 1 To UBound(arr)
n = 3 * (Application.RoundUp(i / 40, 0)) - 2: k = IIf(i Mod 40 = 0, 40, i Mod 40)
brr(k, n) = arr(i, 1): brr(k, n + 1) = arr(i, 2): brr(k, n + 2) = arr(i, 3):
Next i
Sheets("sheet1").[a2].Resize(UBound(arr, 1), 3 * UBound(arr, 2)).ClearContents
Sheets("sheet1").[a2].Resize(UBound(arr, 1), 3 * UBound(arr, 2)) = brr
End Sub
|