ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-LineNumber]C:R[-
1]C)"

This does not work.


the reason that that doesn't work is that it hardcodes the
string 'LineNumber' into your formula, whereas the
term 'LineNumber' only means anything to your program, in
the context of your subroutine.

Use it like this:

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & LineNumber
& "]C:R[-1]C)"



In that, you are entering literal strings for the parts of
the formula that are not variables, but entering the
variable into the cell formula, not the name of the
variable.

Your variable likely actually isn't a string, but I just
tried it and Excel did the conversion on it's own as shown
above.