Using array variable to populate range

Thanks for the response Roy,

the rws variable in the loop is defined here:

Please Login or Register  to view this content.

Sorry if I'm having a "thick" day, but is it not set here?

Please Login or Register  to view this content.

Thank goodness for that - I thought I was being dafter than usual!

It's a mystery to me, but if you're not wedded to your current approach this seems to work:

Please Login or Register  to view this content.

EDIT: on reflection the double increment may be due to the fact that Excel automatically adjusts formulae across a range, added to which you are incrementing in the loop.

Thanks all,

I'm using the array approach because it's quicker. There's over 45000 cells to populate, and using a "normal" loop to write to the sheet every time takes 3-4 minutes.

Any ideas on a workaround?

Arrays are easily written to a range using the Range's Resize property.

Whether you use an array method or another, I would recommend that you turn off calculation, screen updates and events as I did in http://vbaexpress.com/kb/getarticle.php?kb_id=1035

Thanks Kenneth,

I have events, calculations and screenupdate turned off.

Could you further explain the resize method wrt arrays?

Sorted it out. Quickest method I could find was using range.formula for the first line and then range.autofill for the additional.

Thanks all.

Hi Roy,

Thanks, but I still get a double increment in iCount in the spreadsheet. Not to worry - I'll make do with the autofill method.

Thanks to all,

Dave