Assigning a 2D Array to a bigger size range results in "#N/A" in the uncovered elements

I have a 2D array that is being assigned to an Excel range. The range has bigger size than the array:

Please Login or Register  to view this content.

This will result in #N/A in the range where the 2D array did not cover, check the attached image.

A way to work around that is to downsize the range area temporarily, do the assignment then upsize it again, however, I wanted to open this for discussion for better solutions before I implement this to see if there are better solutions.

Ideas?

But the array is strongly typed, what values would I use to fill the newly created elements with ReDim?

A quick and dirty way is as follows, I am not sure about the performance hit compared to downsizing then upsizing (as I want to preserve the original range size)

Please Login or Register  to view this content.

Originally Posted by Norie

Just realised you need to resize both dimensions, so you can't use Redim Preserve.

You don't need to downsize the range though.

Please Login or Register  to view this content.

Thank you, that solves the issue