What is the fastest way to unload a 2 dimensional

Question

I have a routine that was running long due to a big loop that writes values cell by cell in a worksheet. I thought I would be smart and load everything into a 2D array first then quickly dump it into the excel sheet. Goal being to speed things up. Found out I can't just do what I wanted.

  xlsheet.Range("C2") = MASTER_OUT

Found out the above doesn't work. MASTER_OUT is a 2D array with 34 cols and various rows. I hope I don't have to loop through the array and place data cell by cell in the worksheet as that won't save any time at all.

Answer 1

.Resize the destination with the UBound function.

xlsheet.Range("C2").Resize(UBound(MASTER_OUT, 1), UBound(MASTER_OUT, 2)) = MASTER_OUT

Addendum:

It is common to flip the rows and columns. If this is the case then use the Excel Application object's TRANSPOSE function and flip the ubound ranks.

xlsheet.Range("C2").Resize(UBound(MASTER_OUT, 2), UBound(MASTER_OUT, 1)) = _
   Application.Transpose(MASTER_OUT)

Please note that TRANSPOSE has limitations¹. I believe it starts to become unreliable somewhere around an unsigned integer's maximum value (e.g. 65,536 (...?))

---

¹ See Error13 in Excel VBA in data file for a reliable 2D transpose helper function without limitations.