Partial Array into Excel Range

[eAlchemist]

I know how to assign a whole array to a range in Excel. Is there a way to assign a contiguos range of values within an array into a range of cells? I know that I can do it with a loop, but I'm wondering if I can do it in one line.

Thanks,
Chris

P.S. I accidentally listed this in the MS Office forum. Sorry to put it in both, but I don't know how to delete it from the other forum

 

[SkipVought]

Hi,

One line...

for i=0 to ubound(a):cells(i+1,"A").value = a(i):next

Skip,
[sub]
[red][/red]
[/sub]

 

[eAlchemist]

What I was actually looking for was something that will transfer the array in one operation, not one line. This would still require looping through the array, would it not?

I was thinking something more like the way this works for the whole array:

Range(Cells(1,1),Cells(Ubound(a,1),Ubound(a,2))) = a

Thanks,
Chris

 

[N1GHTEYES]

I'd love for someone to tell me I'm wrong, because directly addressing an array sub-set would be a nice feature, but I don't think VB will allow you to do it.

However, there are at least 2 ways round your problem.

Method 1 - (simple but does not do exactly what you asked)
I think addressing cells in excel is generally slower than addressing elements of an array, so you could:
create an array of the right size to take the chunk you want ot assign to the range,
copy the elements from the original array to the slimmed-down array by looping
assign the smaller array directly to the range.

That ought to be faster than addressing the cells in a loop.

Method 2 - More complicated, but faster if you are working with big arrays.

See

http://www.freevbcode.com/ShowCode.Asp?ID=2848.

This shows you how to use copymem to directly insert / extract sections of arrays using the API. Use it to create your smaller array from the required sub-section, then assign directly to the range as above.

Have fun.