|
发表于 2013-9-27 13:53
|
显示全部楼层
本楼为最佳答案
本帖最后由 美斯特邦威 于 2013-9-27 13:56 编辑
douya33 发表于 2013-9-27 13:51
运行时错误‘1004’,未选定要分列的数据. - Sub test()
- Application.ScreenUpdating = False
- Dim pp(1 To 100000, 1 To 3)
- Dim arr, d, k, t, s, brr
- Set d = CreateObject("Scripting.Dictionary")
- arr = Range("a1").CurrentRegion
- [g2:j999].ClearContents
- For i = 2 To UBound(arr)
- s = arr(i, 4) & "|" & arr(i, 5)
- If d.exists(s) Then
- pp(d(s), 1) = pp(d(s), 1) & " " & arr(i, 2)
- pp(d(s), 2) = pp(d(s), 2) + arr(i, 3)
- Else
- d(s) = x + 1
- pp(x + 1, 3) = s
- pp(x + 1, 1) = arr(i, 2)
- pp(x + 1, 2) = arr(i, 3)
- x = x + 1
- End If
- Next i
- Range("g2").Resize(x, 3) = pp
- Application.DisplayAlerts = False
- [i2].Resize(d.Count).TextToColumns Other:=True, OtherChar:="|"
- Application.DisplayAlerts = True
- Set d = Nothing
- Range("g2").CurrentRegion.Sort [i2], 1
- With Range("g2").CurrentRegion
- .Borders.LineStyle = xlContinuous
- .WrapText = True
- .Rows.AutoFit
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 前半部分给你换为数组
OK了,看看如何
|
|