Hello again,

I believe there's a difference in what the OP is
attempting, versus what I'm saying might work. I'm using
2000, which has the same constraints per Tom's link as '97.

Initially I had written this off as the Evaluate Method
miracle, but your response prompted me to reconsider this
some more. Evaluate upon further consideration does not
seem to be the deciding factor here and it will fail with
a data-type array as well.

I am using xl 2000, so f, g & h from Tom's posts apply,
and as I understand it now, this is correct, a data-type
array greater than 5461 elements is not transposable via
WorksheetObject nor the Evaluate method. But, it seems
the same software can transpose more that 5461 range
objects, which it then coerces into a variant data-type
array. So with respect to the original post, in xl 2000 I
see the following:

Sub Does_Not_Work_Xl2000()
Dim rTmp1 As Range
Dim vTmp1() As Variant
Set rTmp1 = Range(Cells(1, 1), Cells(5462, 1))
vTmp1 = Application.WorksheetFunction.Transpose
(rTmp1.Value)
End Sub

Sub Does_Work_Xl2000()
Dim rTmp1 As Range
Dim vTmp1() As Variant
Set rTmp1 = Range(Cells(1, 1), Cells(5462, 1))
vTmp1 = Application.WorksheetFunction.Transpose(rTmp1)
Debug.Print rTmp1(5462)
End Sub

The deciding factor being rTmp1 vs. rTmp1.Value. I also
see the following:

Sub No_Work1()
Dim arr As Variant
arr = Range("a1:a10000")
arr = Application.WorksheetFunction.Transpose(arr)
End Sub

Sub No_Work2()
Dim arr As Variant
arr = Range("a1:a10000")
arr = Evaluate("transpose(" & arr & ")")
End Sub

Sub No_Work3()
Dim arr As Variant
arr = Evaluate("transpose(" & Range("a1:a10000").Value2
& ")")
End Sub

Sub Work1()
Dim arr As Variant
arr = Evaluate("transpose(a1:a10000)")
End Sub

Sub Work2()
Dim arr As Variant
arr = [transpose(a1:a10000)]
End Sub

Sub Work3()
Dim arr As Variant
arr = WorksheetFunction.Transpose(Range("a1:a10000"))
End Sub

And:

Sub Does_Not_Work_Xl2000()
Dim Rng As Variant, arr As Variant
Dim Lst As Long
Lst = 10000
Rng = Evaluate("row(1:" _
& Lst & ")/row(1:" & Lst & ")")
arr = Application.Transpose(Rng)
Debug.Print UBound(arr) & ": " & _
arr(UBound(arr))
End Sub

Sub Does_Work_Xl2000()
Dim Rng As Variant, arr As Variant
Dim Lst As Long
Lst = 10000
Rng = Evaluate("transpose(row(1:" _
& Lst & ")/row(1:" & Lst & "))")
arr = Rng
Debug.Print UBound(arr) & ": " & _
arr(UBound(arr))
End Sub

For the same reasons. So if you want to populate an array
with transpose range object values and you want to
overcome the 5461 limit in xl 2000, transpose the range in
a single step before it gets coerced into a variant data-
type array.

Have a nice weekend.

Regards,
Nate Oliver

-----Original Message-----
Apparently this doesn't work in the OP's version. See the
link in Tom Ogilvy's post in this thread.