How to use Subtotal in VBA
 

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

How to use Subtotal in VBA
 

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.

---
Regards,
Norman



"Wellie" wrote in message
...
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

How to use Subtotal in VBA
 

Hi Wellie,

Const strCol As String = "B"

Should read:

Const strCol As String = "J"

---
Regards,
Norman



"Norman Jones" wrote in message
...
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.

---
Regards,
Norman



"Wellie" wrote in message
...
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