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

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
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"paddy_nyr" <(E-Mail Removed)> wrote in message
news:46a0e432$0$97267$(E-Mail Removed)...
>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
>
>

"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 Removed)
> "paddy_nyr" <(E-Mail Removed)> wrote in message
Thanks Don that did it.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"paddy_nyr" <(E-Mail Removed)> wrote in message
news:46a0f28c$0$97239$(E-Mail Removed)...
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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 Removed)
>> "paddy_nyr" <(E-Mail Removed)> wrote in message
> Thanks Don that did it.
>
>