arr = Array(1, 2, 3, 4, 5)
m = UBound(arr, 1) - LBound(arr, 1) + 1
Set targetrng = Range("A1").Resize(, m)
targetrng.Value = arr

I'm not exactly sure what you mean, inasmuch as the array is not passed
as a parameter to Resize; only the integer values that define the number
of "rows" and "columns" in the array. I would assume that the limits on
m and n in Range("A1").Resize(m, n) are the number of available rows and
columns on a worksheet; i.e., (in current versions of Excel), 65536 and
256, respectively, but I haven't checked what happens if you use larger
numbers.

Alan

Bharath Rajamani wrote:
Thx Alan, this is v useful

If my array has 1 dimension, then how should I use the .Resize example? Is
there an array-size constraint to pass the array as a parameter in Resize

(For e.g. Worksheetfunction.Percentile restricts the array size to 8,xxx
values)


TIA!

BR
---

Capital Markets
GE Capital, London







"Alan Beban" wrote:

m = UBound(arr, 1) - LBound(arr, 1) + 1
n = UBound(arr, 2) - LBound(arr, 2) + 1
Set targetrng = Range("A1").Resize(m, n)
targetrng.Value = arr

Change "A1" as required

Alan Beban

Bharath Rajamani wrote:
Is there a way to paste the entire array directly to a worksheet. Assume the
array is unsorted, dynamically sized using Redim, and has 1mio values, and
can occupy all 256 columns and as many rows as required.

TIA!
BR


--
Capital Markets
GE Capital, London