Totaling only Visable Cells Excel 2007

[I heart Excel]

Hello,

Hope everyone had a nice a weekend!!

I am trying to sum only the visible cells on my spreadsheet.

I have had a look and tried two ways -

Way 1 =

Function VisTotal(Rg as Range)
dim x, tot
Application.Volatile
tot=0
For each x in Rg
if c.visible then tot=tot+x
Next
End Function

in Worksheet -
=VisTotal(E54,E49,E47,E39,E34,E32,E24,E19,E17)

Way 2 =

<CODE>
Function Sum_Visible_Cells(Cells_To_Sum As Object) Application.Volatile For Each cell In Cells_To_Sum If cell.Rows.Hidden = False Then If cell.Columns.Hidden = False Then total = total + cell.Value End If End If Next Sum_Visible_Cells = total End Function </PRE>
In worksheet - =Sum_Visible_Cells(E54,E49,E47,E39,E34,E32,E24,E19,E17)

On both I get a #NAME? error?! And I can't figure out why.

thanks


</CODE>
</PRE>

 

[GlennUK]

I just tried your Sum_Visible_Cells and it worked fine ( with carriage returns in there, no as one long line like you have ).

Can you explain where you are putting the code?

 

[RoryA]

Why not just use SUBTOTAL(109,range)?

 

[GlennUK]

Why not just use SUBTOTAL(109,range)?

Yeah, well you could do it that way ( d'oh!!! )

 

[I heart Excel]

I did the subtotal and this worked great, thanks!!

I was putting the sum at the bottom, tried it again, but still got the error must've been doing something wrong!! I was putting the code on the VBA sheet, should it of been the workbook?!

Thanks for your help

 

[GlennUK]

Your code should have gone into an ordinary Module ( Insert / Module ).