How to fill up a range in Excel worksheet with an array formula using VBA?

Question

Actually the input range is bigger than the actual range required for the array formula as well. So it would be nice if the answer also includes the code to resize the range before filling in with array formula.

Answer 1

This seems to work for me

Call rng.Clear
Dim rngState As Range
Set rngState = rng.Resize(nRowCount, nColumnCount)
rngState.FormulaArray = "whatever_array_formula"
rngState.Calculate