Vba to create a new column and insert array formula My below code is the slowest sub in my workbook. I can't set it to put the Array in immediatley, because the mode is calculated incorrectly (calculates for just one site no)

Is there a way to cut this down without breaking the fully functioning code?

Sub ModeColumn()

Dim wb As Workbook, ws As Worksheet, LastRow As Long, rng As Range

Set wb = ThisWorkbook
Set ws = Worksheets("Data")
Set rng = ws.Cells(2, 15)
    ws.Cells(1, 15) = "MODE"
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row                  'Finds the bottom populated row

    With ws.Range(ws.Cells(2, 15), ws.Cells(LastRow, 15))
           .Formula = "=IFERROR(MODE(IF(RC[-2]=AllSites,R2C12:R" &amp; LastRow &amp; "C12)),""N/A"")"
           .FormulaArray = .FormulaR1C1
    End With


End Sub


Here's what it looks like working




So above we can see that the selection works perfectly, but if I use the array formula right away, I get a lot of errors - so my time saving, breaks the expected result
And here's what it looks like if I just use "array" straight away




This is based on the following code

With ws.Range(ws.Cells(2, 15), ws.Cells(LastRow, 15))
       .FormulaArray = "=IFERROR(MODE(IF(RC[-2]=AllSites,R2C12:R" &amp; LastRow &amp; "C12)),""N/A"")"
End With


P.S I cannot (by request) use Excel Tables to make this easier
Usually with mass-Formula, I would recommend judicious use of the Application.Calculation property to ensure that you don't recalculate for every cell (3773 times in your example!) - but, in this case I am also going to recommend using the Range.FillDown method instead of assigning the Array Formula to every cell in the range.

(Also the MODE function is only included for Legacy Support, and has been replaced by the MODE.SNGL and MODE.MULT functions)

Application.Calculation = xlCalculationManual 'Do not recalculate formula until we say so!
With ws.Range(ws.Cells(2, 15), ws.Cells(LastRow, 15))
       .Cells(1,1).FormulaArray = "=IFERROR(MODE.SNGL(IF($M2=AllSites,$L$2:$L$" &amp; LastRow &amp; ")),""N/A"")" 'Set the Array Formula for the first cell
       '.Cells(1,1).FormulaArray = "=IFERROR(MODE.SNGL(IF(RC[-2]=AllSites,R2C12:R" &amp; LastRow &amp; "C12)),""N/A"")"'In R1C1 Notation
       .FillDown 'Copy the Array Formula down to the end
End With
Application.Calculation = xlCalculationAutomatic 'Reset Calculation Mode


(For comparison - running your method on 99999 rows of junk data took me 105 seconds.  Using the .Filldown method on the same data took 29 seconds, and 99% of that was just waiting for Excel to finish calculating the Worksheet after the functions where in!)