1

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
Community
  • 1
  • 1
Conor
  • 125
  • 1
  • 1
  • 15

1 Answers1

0

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:

enter image description here

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • No that doesn't work. What appears in excel is =SUBTOTAL(9,B2:B) therefore it doesn't calculate – Conor Mar 14 '16 at 14:58
  • @conor - see my edited version. I tested it and it works. – Scott Holtzman Mar 14 '16 at 15:04
  • No still doesn't work. It only returns the value from B2 field. – Conor Mar 14 '16 at 15:07
  • @Conor sorry, I edited it one more time, forgot to subtract 1 from LastCol in offset formula. See screenshot for proof of working. – Scott Holtzman Mar 14 '16 at 15:08
  • Perfect. That works. Would you mind explaining or pointing me in the right direction how you figured out to use (B2,0,0,1," & LastCol-1 & ") – Conor Mar 14 '16 at 15:11
  • read up on the [`Offset`](https://support.office.com/en-us/article/OFFSET-function-C8DE19AE-DD79-4B9B-A14E-B4D906D11B66) function. The -1 is because its starts in column B and you don't want to include the column where the formula is. – Scott Holtzman Mar 14 '16 at 15:13