VBA Subtotals one filter criteria from another column

[Davavo]

I have expenses sheets where various columns of figures need to be totalled according to the filter criteria in the costs centre column.
I had been trying the subtotal 9/109 function expecting that to work, but it seems you can only subtotal if the column is filtered on its own values.

The user imports data into the sheet and the relevant columns total.
The user can then use the filter to isolate a list of any particular cost centre.
The subtotal should then adjust to sum only the visible values.

This si the code I am using

ThisWorkbook.Sheets("CCRead").Range("d" & Lastrow + 1) = Application.WorksheetFunction.Subtotal(9, ThisWorkbook.Sheets("CCRead").Range("d2:d" & Lastrow))

this totally doesnt work.
When filtered on column "M", the subtotal stays the same. WHen filtered on column "D" it changes.

A simple formula works fine... =SUBTOTAL(9,D2:D20)

but this is not good to me because the user needs to be able to add unknown number of rows.

Should the subtotal function be acting like this?
Maybe find last row and paste in a formula?


thanks
Thanks

 

[DanteAmor]

Try this

Sub test()
  Dim lastrow As Long, sh As Worksheet
  Set sh = Sheets("CCRead")
  lastrow = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
  sh.Range("D" & lastrow + 1).Value = WorksheetFunction.Subtotal(9, sh.Range("D2:D" & lastrow))
End Sub

 

[Davavo]

Try this

Sub test()
  Dim lastrow As Long, sh As Worksheet
  Set sh = Sheets("CCRead")
  lastrow = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
  sh.Range("D" & lastrow + 1).Value = WorksheetFunction.Subtotal(9, sh.Range("D2:D" & lastrow))
End Sub

Hi dante, thanks for your response.
That still has the a same problem of not filtering the results when the table is filtered in the "M" column.
Additionally, the usedrange function sends the result to the lowest part of the sheet that has been used recently, rather than the lowest part of the sheet (the bottom of the table) that is in use when it is called.
Clearcontents does not clear whatever it is that the function is looking at, so it necessitates deleting the previously used rows.

I think aggregate is the way forward. I am going to give that a bash. Thanks for your help.

 

[DanteAmor]

It worked for me, because you don't try to put the result in a variable before taking it to the filtered sheet, for example:

Sub test()
  Dim lastrow As Long, sh As Worksheet
  Set sh = Sheets("CCRead")
  lastrow = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
  [COLOR=#0000ff]MsgBox [/COLOR]WorksheetFunction.Subtotal(9, sh.Range("D2:D" & lastrow))
End Sub