Calculating Only Visiable Data

  • Thread starter Thread starter paddy_nyr
  • Start date Start date
P

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:p" & 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:p" & X))
a_tcc = Application.Average(Range("v2:v" & X))
a_aip = Application.Average(Range("w2:w" & X))

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


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

End Sub
 
You don't need a macro to calculate the average of filtered cells. Just use
this worksheet function:
=SUBTOTAL(1, your entire range here)
 
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 said:
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
Click to expand...
Thanks Don that did it.
 
Back
Top