Dynamic Range Subtotal Calculation

[Jasvindra]

Below is My COde

Sub Subtotal()
'
' Macro1 Macro
'
Dim rwMax As Integer
Dim MYRANGE As Range
rwMax = Cells(Rows.Count, 1).End(xlUp).Row
Range("B" & rwMax + 1).Select

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,B2:B" & rwMax & ")"
End Sub

But I am getting =SUBTOTAL(9,'B2':'B16') in the result cell

 

[Fluff]

It needs to be .Formula, not .FormulaR1C1

 

[nardagus]

Try this:

Sub Subtotal()'
' Macro1 Macro
'
Dim rwMax As Integer
Dim MYRANGE As Range
rwMax = Cells(rows.Count, 1).End(xlUp).row
Range("B" & rwMax + 1).Select


ActiveCell.Formula = "=SUBTOTAL(9,B2:B" & rwMax & ")"
End Sub

 

[Jasvindra]

Great

It worked

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Jasvindra]

Hi Nardagus

I am using the below formula in one of my Macro. In which this subtotal cell is copied with respective value for which filter has been applied and pasted on next sheet.

But Macro has been giving same Total(Total without filter) value for each loop.
May be it is because I am using rwmax in formula which refers to last row in the data.
Could you please help me

ActiveCell.Formula = "=SUBTOTAL(9,B2:B" & rwMax & ")"

 

[Fluff]

Try

ActiveCell.Formula = "=SUBTOTAL([COLOR=#ff0000]109[/COLOR],B2:B" & rwMax & ")"

 

[Jasvindra]

It is giving the same result.

Try

ActiveCell.Formula = "=SUBTOTAL([COLOR=#ff0000]109[/COLOR],B2:B" & rwMax & ")"

 

[Fluff]

Can you post the code that you are using.