Get Average on results of Autofilter

I have an excel spreadsheet has the auto filter turned on. After I
filter the data for a specific user, I would like to get an average of
a column of just those filter results. Is there an easy way to do this
in my VBA code? I am using the average in my code for further
calculations so I dont want to use the =SUBTOTAL function to display it
on the worksheet.

Any ideas?

Thanks

One way

Sub test()
Dim nm As Name
Dim x As Double

On Error Resume Next

Set nm = ActiveSheet.Names("_FilterDatabase")

If Not nm Is Nothing Then
'avg of 2nd col of the filter range
x = Application.Subtotal(1, Range(nm).Columns(2))
End If

End Sub

Regards,
Peter T

<[email protected]> wrote in message
news:[email protected]...
> I have an excel spreadsheet has the auto filter turned on. After I
> filter the data for a specific user, I would like to get an average of
> a column of just those filter results. Is there an easy way to do this
> in my VBA code? I am using the average in my code for further
> calculations so I dont want to use the =SUBTOTAL function to display it
> on the worksheet.
>
> Any ideas?
>
> Thanks
>

Just to clarify is "_FilterDatabase" the already filtered result set?
or would that be the entire worksheet?

"_FilterDatabase" is a hidden Worksheet name applied by Excel that defines
the current filter range, or the last one if filter has been removed (the
name persists after removing the filter). If not removed by other means the
formula will still work. That may or may not be useful for you.

Plenty of other ways, probably you already know the filter range and if a
filter is applied, so use that in a similar formula.

If ActiveSheet.AutoFilterMode Then
Set r = ActiveSheet.AutoFilter.Range
MsgBox r.Address
End If

Or loop through non hidden rows.

Regards,
Peter T


<[email protected]> wrote in message
news:[email protected]...
> Just to clarify is "_FilterDatabase" the already filtered result set?
> or would that be the entire worksheet?
>