Subtotal in Autofilter Referencing

[Bob Zimski]

I import data which is parsed and formatted by VB. In the end, I set an Auto
filter so I can do my analysis. What I am trying to accomplish is to have the
macro put in the subtotal(9,.....) for me. The data length is variable, but
obviously I am not referencing things correctly because I am getting an
error. Perhaps there is even a better way to accomplish what I want.

Thanks


Dim Maxrows As Long
Maxrows = ActiveSheet.UsedRange.Rows.Count
Cells(Maxrows + 3, 5).Select
ActiveCell.Formula = "=SUBTOTAL(9,range(cells(2,5),cells(maxrows,5)"

 

[RadarEye]

Hi Bob,

if you try this you should get a result.
I used =SUBTOTAL(109,....) to get only the SUM of visible rows.

Dim lastRow As Long
lastRow = Cells(2, 5).End(xlDown).Row

Cells(lastRow + 3, 5).Select
ActiveCell.Formula = "=Subtotal(109, E2:" & _
Cells(lastRow, 5).Address & ")"

HTH,

Wouter