Unable to AutoSum with LastCol in formula

Question

Im having an issue writing VBA to Autosum where columns may increase or decrease from time to time. Take below as an example. I have set my LastCol to find the last column, i then want to autosum from column B on the row to the last column to get my 'Total. I want to avoid R1C1 Formulas where possible. Also the RC[-4] will change depending how many columns are on spreadsheet.

Sub AutoSum()

    Dim LastCol As Integer

    Sheets("Sheet1").Select
    LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

    Cells(2, LastCol1 + 1).Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,RC[-4]: RC[-1])"

End Sub

Answer 1

Give this a shot:

Sub AutoSum()

    Dim LastCol As Integer

    With Sheets("Sheet1")

        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        .Cells(2, LastCol1 + 1).Formula = "=SUBTOTAL(9,Offset(B2,0,0,1," & LastCol-1 & "))"

    End With

End Sub

After running above code: