I'm running a simple macro (below) that uses the worksheet function TRANSPOSE with Excel 2007 on a worksheet that contains 1,048,576 rows. I've observed that if the code input value N is greater than 65,536 (the number of rows in Excel 2003 worksheets), I get a run time error 13 (type mismatch) on the line that uses the Transpose function. It appears that this function cannot be used to transpose a one-dimensional array larger than 65,536 elements, but I cannot find any mention of this limit in the Excel help documentation. Can anyone confirm this is a known limit or am I missing something?
Rich (BB code):
Sub GetDist() Dim arr(), N As Long Application.Calculation = xlCalculationManual N = Application.InputBox("How many trials?", 1) ReDim arr(0 To N - 1) Application.ScreenUpdating = False With Range("B2") For i = 0 To N - 1 arr(i) = Int(Rnd * 10) + 1 Next i .Resize(N, 1).Value = Application.WorksheetFunction.Transpose(arr) End With Application.Calculation = xlCalculationAutomatic End Sub