Unable to AutoSum with LastCol in formula
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
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: