Using subtotal

[Jakobshavn Isbrae]

I have data in a column from A2 down to around A800. The data is filtered by
auto filter.

I can get the sum of the visible data with =SUBTOTAL(109,A1:A1000). This
works just fine. Now I want to get the sum of only the last 7 visible
values. Problem is that I don’t know how to put in the range because I don’t
know it until after the filter is applied.

This is probably very easy, but excel help for subtotal does not cover it.

 

[Don Guillett]

Sub sumlastSevenvisible()
mc = 1
On Error Resume Next
For i = Cells(Rows.Count, mc).End(xlUp).row To 1 Step -1
If Rows(i).Hidden = False Then
mycount = mycount + 1
mysum = mysum + Cells(i, mc)
If mycount = 7 Then Exit For
End If
Next i
MsgBox mysum
End Sub

 

[Don Guillett]

or a UDF where =sl("a",7) or =sl(1,5)

Function sl(col, num)
mc = col
On Error Resume Next
For i = Cells(Rows.Count, mc).End(xlUp).row To 1 Step -1
If Rows(i).Hidden = False Then
mycount = mycount + 1
mysum = mysum + Cells(i, mc)
If mycount = num Then Exit For
End If
Next i
sl = mysum
End Function