Limitation on Range Size for Transpose Function?

[JoeMo]

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?

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

 

[mirabeau]

There's a bit of a discussion in this thread

http://www.mrexcel.com/forum/showthread.php?t=526629&highlight=transpose

which may (or maybe not?) be of some interest to you.

 

[JoeMo]

Mirabeau,
Thanks for your reply. I looked at the thread you provided. Post #5 of that thread (from you) includes this quote:

However there's a limit on the size of the transpose function to 65536 even for Excel 2007 (I don't know about later versions). With more than this you may get a Runtime Error 13 or something like, if you try transposing.

To display a million or so entries of the array x down a single column you really have to use ReDim x(1 to n, 1 to 1) - or equivalent of you want to change Option Base. In this case there's no problem with Transpose limitations.

Based on this, I revised my code as shown below, but still encounter the run time error 13 if N > 65,536. Am I misinterpreting your suggested fix?

Sub GetDist()
Dim arr(), N As Long
Application.Calculation = xlCalculationManual
N = Application.InputBox("How many trials?", Type:=1)
ReDim arr(1 To N, 1 To 1)
Application.ScreenUpdating = False
With Range("B2")
    For i = 1 To N
        arr(i, 1) = Int(Rnd * 10) + 1
    Next i
    .Resize(N, 1).Value = Application.Transpose(arr)
End With
Application.Calculation = xlCalculationAutomatic
End Sub

 

[mirabeau]

Maybe I'm a bit unclear as to just what you want to do, but it looks as though your code should run OK if you do it as

Sub GetDist()
Dim arr(), N As Long
Application.Calculation = xlCalculationManual
N = Application.InputBox("How many trials?", Type:=1)
ReDim arr(1 To N, 1 To 1)
Application.ScreenUpdating = False
With Range("B2")
    For i = 1 To N
        arr(i, 1) = Int(Rnd * 10) + 1
    Next i
    .Resize(N, 1).Value = arr   'Application.Transpose(arr)
End With
Application.Calculation = xlCalculationAutomatic
End Sub

Incidentally, setting ScreenUpdating = False should make negligible difference to the speed of this particular code - it might even slow it down a bit.

If you want that section to run somewhat faster, you might try

With Range("B2").Resize(N) 'or Resize(N,1) if you like
    .Cells = "=Int(Rand()*10)+1"
    .Value = .Value
End With

Although maybe you have some other reason for wanting to transpose and/or calculate arr explicitly instead of just using a worksheet display of the equivalent thing.

 

[JoeMo]

Thanks. The code itself has no real purpose. I'm more interested in understanding why Transpose is limited to 65,536 elements. The fix you suggested in the post you referred me to does not work for me. If I create an array Arr(1 to 1, 1 to N), which is a 1 x N horizontal array, with N > 65,536, I cannot enter it in a vertical (one-column) range on an Excel 2007 worksheet having 1,048,576 rows using the worksheet function Transpose. When I try to do this the result is a run time 13 error. There is no error if N<= 65,536.

 

[mirabeau]

Thanks. The code itself has no real purpose. I'm more interested in understanding why Transpose is limited to 65,536 elements. The fix you suggested in the post you referred me to does not work for me. If I create an array Arr(1 to 1, 1 to N), which is a 1 x N horizontal array, with N > 65,536, I cannot enter it in a vertical (one-column) range on an Excel 2007 worksheet having 1,048,576 rows using the worksheet function Transpose. When I try to do this the result is a run time 13 error. There is no error if N<= 65,536.

The Transpose function just doesn't work with >65536, just as SpecialCells or AdvancedFilter etc.methods sometimes don't work with ranges above a certain size. You'd have to ask the Microsoft coders why they haven't updated these to allow for the larger sheet size of Excel 2007 and subsequent versions.

The fix I suggested wasn't intended to make the Transpose function work in cases where it doesn't. There are other approaches to transposing. The Transpose function may be convenient when it works, but if it doesn't work this doesn't stop you transposing, perhaps in ways that are more efficient anyway.

In your example (in blue above) you can do that with the Transpose Function by splitting your 2^20 (or 1,048,576) range into sections, each less than 2^16, and use the Transpose Function on each, but why anyone would want to this I can't imagine, since there are more convenient and faster ways of transposing large vectors, such as index swapping or (in your case) defining your array as Arr(1 to N, 1 to 1) from the start instead of initially defining its transpose and then trying to transpose that using a function which was never constructed to work in that case anyway.