0

I am running a macro that I have the different rows every day and in the and I would like to create a subtotal.

as example:

Sheets("RBC_data").Select

Range("R1").Select

Selection.End(xlDown).Select

ActiveCell.Offset(rowoffset:=1).Activate

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R.....

How I can write the code for the formula select the row R2 to the end and make the formula subtotal?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Robson Pereira
  • 11
  • 1
  • 1
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jan 20 '20 at 09:09
  • 2
    There are many posts on SO on how to get the last used row dynamically. With that you'll be able to pull in a dynamic `Range` and work with that. – JvdV Jan 20 '20 at 09:22

1 Answers1

1

Believe me, you can't get answers faster from a forum than you can get them by doing a little research on your own. Everybody here knows that. Therefore they guide you to stand on your own two feet.

Here's the answer to your question. Note that it will insert the formula on the ActiveSheet, meaning the tab you are looking at when you call the macro or the one you were last looking at before you switched to the VB Editor. If you need to specify another sheet, do a little research. It's quick and easy. But avoid selecting anything, as PEH said.

Sub InsertSubtotal()

    Dim Cell As Range

    Set Cell = Cells(Rows.Count, "R").End(xlUp)
    Cell.Offset(1).Formula = "=SUBTOTAL(9,$R$2:" & Cell.Address & ")"
End Sub
Variatus
  • 14,293
  • 2
  • 14
  • 30