Help for subtotal

[pol]

Please help in the following example how I can give end of column
instead of O193


ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,O3:O193)"

with thanks and regards

Pol

 

[Mike H]

Hi,

Maybe this

lastrow = Cells(Rows.Count, "O").End(xlUp).Row
ActiveCell.Formula = "=SUBTOTAL(9,O3:O" & lastrow & ")"

Mike

 

[pol]

Thanks Mike,


When I am using that formula , the subtotal is showing just last line of the
column. I want the the subtotal should be shown at the end of the data. But
when I am executing that formula , the result will be overwritten the data on
the last line . So I given the following mathod but it is not working when I
am filtering

With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("O2:O" & LastRow).FormulaR1C1 = .Range("O2").FormulaR1C1
End With
Range("O" & LastRow + 1).Select
LastRow = Cells(Rows.Count, "O").End(xlUp).Row
ActiveCell.Formula = "=SUBTOTAL(9,O3:O" & LastRow & ")"

Please Help

 

[Mike H]

see your latest post

Thanks Mike,


When I am using that formula , the subtotal is showing just last line of the
column. I want the the subtotal should be shown at the end of the data. But
when I am executing that formula , the result will be overwritten the data on
the last line . So I given the following mathod but it is not working when I
am filtering

With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("O2:O" & LastRow).FormulaR1C1 = .Range("O2").FormulaR1C1
End With
Range("O" & LastRow + 1).Select
LastRow = Cells(Rows.Count, "O").End(xlUp).Row
ActiveCell.Formula = "=SUBTOTAL(9,O3:O" & LastRow & ")"

Please Help

 

[Nastech]

see your latest post