Sending an Array to a Range without a Do Loop

  • In a macro I can have a line like
    x=range("A1:A65000")
    that quickly puts all of the values in the range into the variant x.


    Now suppose I have a variant y with 65000 values that I want to put to range("A1:A65000"). How can I do that without a do loop? range("A1:A65000") =y fails.

  • Thanks Richie, but it doesn't work. Only the first value in the variant array is copied to all of the range cells, rather than y(1) to the first cell, y(2) to the second and so on. A do loop does what I want, but I'm trying to save execution time.

  • With some experimentation, I've found the solution. The variant needs to be two dimensional for it to work correctly when passing values back. Try the following macro (after you have some values in A1:A5). All but the last works correctly. In my application I was trying the equivalant of the last rather than the third column.


    My apologies Richie, I should have given you some more context.

  • Hi Derk,


    It's me that should apologise - my example was rushed because I was just about to go to the pub! :biggrin:


    Anyway, this is what I should have written:

    and your conclusion is correct, the array does need to be 2D to reflect the fact that it is being transferred to a range.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!