R1C1 Formula for subtotal

Hi Guys,
I am writing a lengthy macro and am stuck in a part where I want to enter a subtotal formula in a cell. Since the whole macro would be creating many sheets and performing many actions on them, therefore, i want this formula to use R1C1 format for subtotal. But at the same time I want it to take a range which will start from a static cell e.g. U2 and go till the final row of U column.

When recording the same it gave me the below script:

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

This does not cater my problem as, the sheet would have different no. of rows every time, therefore I want the formula to take the range from cell U2 to final row of U column. I tried tweaking it as below but it wouldn't work

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,"U2:U" & Finalrow)"

Any help is appreciated.
Thanks... Murtaza

try:

Please Login or Register  to view this content.

not sure if the syntax is correct, but since Finalrow is the last row used in the U column and you are subracting it from the activecell which is one more than the final row, it should give you the correct begining row

given that Finalrow is derived correctly, and for each sheet that the formula will go in

also, since you are starting from a static cell that is the same over sheets then you don't need to use R1C1. R1C1 is used if there are multiple columns and rows involved and the formula itself is in multiple places

Please Login or Register  to view this content.

should work too