Calculating Only Visiable Data
 

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

Calculating Only Visiable Data
 

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

Calculating Only Visiable Data
 

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

"paddy_nyr" wrote in message
reenews.net...
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

Calculating Only Visiable Data
 

"Don Guillett" wrote in message
...
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

"paddy_nyr" wrote in message
Thanks Don that did it.

Calculating Only Visiable Data
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"paddy_nyr" wrote in message
reenews.net...
"Don Guillett" wrote in message
...
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

"paddy_nyr" wrote in message
Thanks Don that did it.