1
\$\begingroup\$

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" & LastRow & "C12)),""N/A"")"
           .FormulaArray = .FormulaR1C1
    End With


End Sub

Here's what it looks like working

enter image description here enter image description here

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

enter image description here enter image description here

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" & LastRow & "C12)),""N/A"")"
End With

P.S I cannot (by request) use Excel Tables to make this easier

\$\endgroup\$
  • 1
    \$\begingroup\$ Just from the images in the top you have $L$2:$L$3774 and below it you have $L$2:$L7. Seems like something was done that's not reflected in your code. Was there a manual edit? \$\endgroup\$ – IvenBach Mar 13 at 18:06
  • \$\begingroup\$ Sorry, should have mentioned - the last 2 images are from the "record macro" feature, where I added in the Array Forumla after \$\endgroup\$ – Badja Mar 14 at 9:15
  • \$\begingroup\$ I have edited my question to reflect the bad code correctly \$\endgroup\$ – Badja Mar 14 at 9:26
1
\$\begingroup\$

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$" & LastRow & ")),""N/A"")" 'Set the Array Formula for the first cell
       '.Cells(1,1).FormulaArray = "=IFERROR(MODE.SNGL(IF(RC[-2]=AllSites,R2C12:R" & LastRow & "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!)

\$\endgroup\$
  • \$\begingroup\$ Hi - thanks for the great insight. Clearly looks like a much better way to run this. I'm not sure what I need to do with Application.Calculate as it just gives me a compile error. Apologies for this, I've never used this feature \$\endgroup\$ – Badja Mar 18 at 9:59
  • \$\begingroup\$ Got it, I used Application.Calculation = xlCalculationManual instead \$\endgroup\$ – Badja Mar 18 at 10:05
  • 1
    \$\begingroup\$ @Badja Yes, I was just about to say that - I typed the wrong one when writing it out for your code. Sorry! \$\endgroup\$ – Chronocidal Mar 18 at 10:06
  • 1
    \$\begingroup\$ @Badja The Range.FormulaArray property is known to sometimes have... issues... with R1C1 notation, which is why I rewrote "=IFERROR(MODE.SNGL(IF(RC[-2]=AllSites,R2C12:R" & LastRow & "C12)),""N/A"")" as "=IFERROR(MODE.SNGL(IF($M2=AllSites,$L$2$L$" & LastRow & ")),""N/A"")" (the same, but in A1 notation) \$\endgroup\$ – Chronocidal Mar 18 at 10:16
  • 1
    \$\begingroup\$ I was missing the colon from "$L$2:$L$" & LastRow \$\endgroup\$ – Chronocidal Mar 18 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.