Using subtotal
 

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 dont know how to put in the range because I dont
know it until after the filter is applied.

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

--
jake

Using subtotal
 

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
Microsoft MVP Excel
SalesAid Software

"Jakobshavn Isbrae" wrote in
message ...
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 dont know how to put in the range because I
dont
know it until after the filter is applied.

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

--
jake

Using subtotal
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
Microsoft MVP Excel
SalesAid Software

"Jakobshavn Isbrae" wrote in
message ...
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 dont know how to put in the range because I
dont
know it until after the filter is applied.

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

--
jake