[已解决]引用工作表函数Index处理在VBA里超过 65536行，出错了怎么办

laoau138

引用工作表函数Index处理在VBA里超过 65536行，出错了怎么办


Sub test()
    Dim arr1, arr2(1 To 4), Maxrow As Long, x As Long, y As Long, t As Single
    t = Timer
    Maxrow = Cells(Rows.Count, 1).End(xlUp).Row
    arr1 = Range("A1:E" & Maxrow)
    For x = 1 To Maxrow
        For y = 1 To 4
            arr2(y) = arr1(x, y)
        Next y
        arr1(x, 5) = Join(arr2, "-")
        Erase arr2
    Next x '

    [E1].Resize(Maxrow, 1) = Application.WorksheetFunction.Index(arr1, 0, 5)

    '引用工作表函数Index处理在VBA里超过 65536行，出错了怎么办

    Columns(5).AutoFit
    MsgBox "用时" & Format(Timer - t, "0.00秒")
End Sub

最佳答案

月排行榜 / 总排行榜

dsmch

2017-2-22 15:41

1. Sub Macro1()
   
2. Dim arr, brr, i&, j%
   
3. arr = Range("a1").CurrentRegion
   
4. ReDim brr(1 To UBound(arr), 1 To 1)
   
5. For i = 1 To UBound(arr)
   
6.     p = ""
   
7.     For j = 1 To 4
   
8.         p = p & "-" & arr(i, j)
   
9.     Next
   
10.     brr(i, 1) = Mid(p, 2)
    
11. Next
    
12. Range("e1").Resize(UBound(brr)) = brr
    
13. End Sub

复制代码

跳转到最佳答案楼层

dsmch

1. Sub Macro1()
   
2. Dim arr, brr, i&, j%
   
3. arr = Range("a1").CurrentRegion
   
4. ReDim brr(1 To UBound(arr), 1 To 1)
   
5. For i = 1 To UBound(arr)
   
6.     p = ""
   
7.     For j = 1 To 4
   
8.         p = p & "-" & arr(i, j)
   
9.     Next
   
10.     brr(i, 1) = Mid(p, 2)
    
11. Next
    
12. Range("e1").Resize(UBound(brr)) = brr
    
13. End Sub

复制代码

laoau138

dsmch 发表于 2017-2-22 15:41

VBA用两种方法按列连接起来


http://www.excelpx.com/thread-427966-1-1.html