Macro variable column length sum

[uncleslinky]

hi there
Is there a way to change the code below so that when I sum data excel doesn't go to row "30000"
If possible I would like it to stop at the last cell of data in cell A

Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
    
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C3:R30000C3,RC3,R2C[-12]:R30000C[-12])"
    Selection.AutoFill Destination:=Range("U2:U" & r)

As you can see I have done this for the last line of code where it copies down the formula, however I can't figure out how to do this on the 3rd line of code
Thanks

 

[Airfix9]

Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
    
    ActiveCell.FormulaR1C1 = "=SUMIF(R2C3:R" & r & "C3,RC3,R2C[-12]:R" & r & "C[-12])"
    Selection.AutoFill Destination:=Range("U2:U" & r)

 

[uncleslinky]

hi, thats great thanks!

Can i just ask another quick question...

Do you know of a way I can add together the last cell in column J, M, N, O and record this at the bottom of column T, offset by 5 rows

Thanks again

 

[Airfix9]

This should work

sub Test()

dim lRow as long
lRow = sheets("MySheetName").Range("A1048576").end(xlup).row ' if using XL03, change the 1048576 to 65536

Sheets("MySheetName").Range("T" & lRow + 5).FormulaR1C1 = "=SUM(R" & lRow & "C10,R" & lRow & "C13:R" & lRow & "C15)"

End sub

You are basically building strings from defined data and variables.