VBA Excel:将大数组粘贴到范围 [英] VBA Excel: Paste large array to range

问题描述

我正在VBA中使用Excel.我有一个名为"aKey"的数组(1×137,000个字符串，但是确切的大小可能会发生变化，因此使代码通用是必要的).我需要将aKey粘贴到工作表的第一列.到目前为止，我已经尝试过 Range(.Offset(1,0),. Offset(UBound(aKey)+1,0)).Value = aKey 但这似乎只粘贴了数组的第一个条目的137,000个版本.我也尝试过 Range(.Offset(1,0),. Offset(UBound(aKey)+1,0)).Value = WorksheetFunction.Transpose(aKey)这也没有用.通过谷歌搜索，我确实发现移调"功能的粘贴大小有限，这很可能就是那里的问题.有人知道实现我目标的方法吗?谢谢

I'm working in VBA for excel. I have an array called "aKey" (1 by 137,000 strings but exact size is subject to change so making code generic is a neccesity). I need to paste aKey to the first column of a a worksheet. So far i have tried Range(.Offset(1,0),.Offset(UBound(aKey)+1,0)).Value = aKey but this seems to only paste 137,000 versions of the first entry of the array. I have also tried Range(.Offset(1,0),.Offset(UBound(aKey)+1,0)).Value = WorksheetFunction.Transpose(aKey) which also didn't work. Through a google search I did find that the Transpose function has a limited pasting size which may very well be the problem there. Does anybody know of a method to avhieve my goal? Thanks

推荐答案

Sub Tester()

    Dim a1(), a2(), i As Long, ub As Long

    ReDim a1(1 To 1, 1 To 137000)
    'load source array ("wrong" shape)
    For i = 1 To 137000
        a1(1, i) = i
    Next i

    ub = UBound(a1, 2)

    ReDim a2(1 To ub, 1 To 1) 'resize a2 ("right" shape) to match a1

    ' "flip" the a1 array into a2 
    For i = 1 To ub
        a2(i, 1) = a1(1, i)
    Next i

    'drop a2 to worksheet
    ActiveSheet.Range("a1").Resize(ub, 1).Value = a2

End Sub

这篇关于VBA Excel:将大数组粘贴到范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！