Summing (un)filtered amounts

[Brian.Crawford]

I want to sum a range (a column) of only the rows that are not (auto)filtered (ie the visible ones). I have tried summing the range by "not .hidden" as per below but it still sums everything.
Suggestions please. The typical code I use is;
.
AdjSumP7 = Format(SumVisibleCells(Range("AdjP7")), DlrFmt)
.
where
.
Function SumVisibleCells(CellsToSum As Object)
Application.Volatile
Dim Total As Double
For Each cell In CellsToSum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
Total = Total + cell.Value
End If
End If
Next
SumVisibleCells = Total
End Function

 

[shg]

Perhaps ...

Function SumVisibleCells(CellsToSum As Range) As Double
    SumVisibleCells = WorksheetFunction.Subtotal(9, CellsToSum)
End Function

 

[Brian.Crawford]

works great, too bad the "help" doesn't tell you how it works with filtered data
thanks

 

[shg]

I think it does:

The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.