Sending an Array to a Range without a Do Loop

  • In a macro I can have a line like
    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.

  • Derk,

    You can use the Application.Transpose(vaData) for reading the data back.

    It´s limit to 5436 cells upto XL 2000 while in 2002 this limitation does not exit.

    Make a search here on the board where I have used the above approach.

    Kind regards,

  • 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!