|
发表于 2013-2-25 22:34
|
显示全部楼层
本楼为最佳答案
lxing20 发表于 2013-2-25 22:07
谢谢老师及时关注,函数没去试过,因为数据量比较多,还要写入别的工作表,所以想用VBA来解决,能帮我试试 ... - Sub test()
- Dim arr, brr(), x&, i&, j&
- j = 1
- arr = Range("B5:D16")
- ReDim brr(1 To 4, 1 To j + 3)
- For x = 1 To UBound(arr) Step 4
- For y = 1 To UBound(arr, 2)
- If arr(x, y) <> "" Then
- i = i + 1
- If i > 4 Then
- i = 1: j = UBound(brr, 2) + 1
- ReDim Preserve brr(1 To 4, 1 To j + 3)
- End If
- brr(i, j) = arr(x, y)
- brr(i, j + 1) = arr(x + 1, y)
- brr(i, j + 2) = arr(x + 2, y)
- brr(i, j + 3) = arr(x + 3, y)
- End If
- Next y
- Next x
- Range("L16:O65536").Clear
- Range("L16").Resize(UBound(brr, 2), 4) = Application.Transpose(brr)
- Range("L16").Resize(UBound(brr, 2), 4).Borders.LineStyle = 1
- End Sub
复制代码
三列转四列用VBA实现.rar
(9.65 KB, 下载次数: 7)
|
|