How to use Subtotal in VBA

[Guest]

I have a spreadsheet used to store data collected and plan to write some VBA
codes associate to a button. I have a test routine using the Filter function
as shown in sample below

Selection.AutoFilter Field:=1, Criteria1:="MLI"
Selection.AutoFilter Field:=6, Criteria1:="Cost Performance Index"
Selection.AutoFilter Field:=7, Criteria1:="Computed Metric"

Can someone please tell me how can use the "Subtotal" function as if it were
on the spreadsheet to sum and average of the above filtered data stored in
column J ?

Thanks in advance for any assistance

 

[Norman Jones]

Hi Wellie;

Try something like:

Sub Tester()
Dim rng As Range
Dim MyTotal As Double
Dim MyAverage As Double
Const strCol As String = "B"

Set rng = ActiveSheet.AutoFilter.Range

With rng
.AutoFilter Field:=1, Criteria1:="MLI"
.AutoFilter Field:=6, Criteria1:="Cost Performance Index"
.AutoFilter Field:=7, Criteria1:="Computed Metric"
End With

With Application
MyTotal = .Subtotal(9, rng.Columns(strCol))
MyAverage = Format(.Subtotal(1, rng.Columns(strCol)), "0.00")
End With

MsgBox "Filter Total= " & MyTotal & vbNewLine _
& "Filter Average = " & MyAverage

End Sub

Note that this includes no error checking.

 

[Norman Jones]

Hi Wellie,

Const strCol As String = "B"

Should read:

Const strCol As String = "J"