- Sub test()
- Dim arr, i&, re, jg%, rng As Range, maxcol%, col%
- Set rng = Range("C1") '设置导出的区域
- jg = 15 '设置每行间隔
- maxcol = Columns.Count - rng.Column + 1
- arr = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
- col = UBound(arr) \ jg + IIf(UBound(arr) Mod jg = 0, 0, 1)
- ReDim re(1 To jg * (col \ maxcol + 1), 1 To IIf(maxcol < col, maxcol, col))
- For i = 1 To UBound(arr)
- re((i - 1) Mod jg + 1 + Int((i - 1) / maxcol / jg) * jg, (i - 1) \ jg Mod maxcol + 1) = arr(i, 1)
- Next
- rng.Resize(UBound(re), UBound(re, 2)) = re
- Range("A1").Copy
- rng.Resize(UBound(re), UBound(re, 2)).PasteSpecial Paste:=xlPasteFormats
- Application.CutCopyMode = False
- End Sub
复制代码 |