Limitation on Range Size for Transpose Function?

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
Code:
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

Banned user
Joined
Nov 4, 2010
Messages
2,075
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
Rich (BB code):
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
Rich (BB code):
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Banned user
Joined
Nov 4, 2010
Messages
2,075
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,632
Messages
5,626,000
Members
416,150
Latest member
Dawzzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top