Calculating Only Visiable Data

[paddy_nyr]

I have a multi column spreadsheet and let's say that in column P I have
numeric values. I then filter the data and I only want to calulate the
average on what visable on the screen.

I created this sub routine to average my column datasets, but again when the
user filters I'd like to calculate on the visable dataset.

I'm using Excel 2003 running Windows XP

Thanks


Sub AverageData()

Dim a_avg As Currency
Dim a_tcc As Currency
Dim a_aip As Currency

Dim a_test As Currency

X = Cells(Rows.Count, "c").End(xlUp).Row

Cells(X + 2, "p") = Application.Average(Range("p2" & X))
Cells(X + 2, "v") = Application.Average(Range("v2:v" & X))
Cells(X + 2, "w") = Application.Average(Range("w2:w" & X))


a_avg = Application.Average(Range("p2" & X))
a_tcc = Application.Average(Range("v2:v" & X))
a_aip = Application.Average(Range("w2:w" & X))

a_avg = Application.Average(Range("p2"))


MsgBox " Your new Avg Salary is now : " & a_avg

End Sub

 

[Anony]

You don't need a macro to calculate the average of filtered cells. Just use
this worksheet function:
=SUBTOTAL(1, your entire range here)

 

[Don Guillett]

Sub avervisible()
mysum = 0
On Error Resume Next
For Each c In Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)
If c.EntireRow.Hidden <> True Then
mysum = mysum + c.Value
mc = mc + 1
End If
Next
MsgBox mysum
MsgBox mc
MsgBox mysum / mc
End Sub

 

[paddy_nyr]

Sub avervisible()
mysum = 0
On Error Resume Next
For Each c In Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)
If c.EntireRow.Hidden <> True Then
mysum = mysum + c.Value
mc = mc + 1
End If
Next
MsgBox mysum
MsgBox mc
MsgBox mysum / mc
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"paddy_nyr" <[email protected]> wrote in message

Thanks Don that did it.

 

[Don Guillett]

Glad to help. Subtotal is better but you asked for VBA