CODEWITHSUNDEEP
excelvba
Han
Han

Reputation: 21

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

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.

Upvotes: 1

Views: 2008

Answers (2)

Air_Cooled_Nut
Air_Cooled_Nut

Reputation: 138

What I was looking for but just my more thorough version given that the array has already been populated:

Sub PasteArray(vTheArray As Variant)
Dim rPasteHere As Range

With ActiveWorkbook
    Set rPasteHere = .Sheets("PayRoll").Range("A1").CurrentRegion  'Assign the region to use
    With rPasteHere
        .Clear  'Wipe the current region clean
        Set rPasteHere = .Resize(UBound(vTheArray, 1) + 1, UBound(vTheArray, 2) + 1)  'Resize the region to your input
        .FormulaArray = vTheArray  'Dump the array into the resized region
    End With
End With
Set rPasteHere = Nothing  'Clean up!
End Sub

Remember that arrays are zero based, thus the +1 in the .Resize function. For my application I hard-coded the sheet name and range so naturally how rPasteHere comes to be is up to the individual.

Upvotes: 0

Han
Han

Reputation: 21

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

Upvotes: 1

Related Questions