Summing visable cells only

[Paully Shore]

I have a worksheet with 8000+ rows. Some of the rows are hidden. I want to
have a formula at the bottom of the rows that sums only the visable cells
(non-hidden) rows. Any suggestions? (The rows are hidden and not filtered).
--
Thanks Buuuuddddy!

[David Billigmeier]

=SUBTOTAL(109,A1:A8000) will give you what you need.


--
Regards,
Dave


"Paully Shore" wrote:

> I have a worksheet with 8000+ rows. Some of the rows are hidden. I want to
> have a formula at the bottom of the rows that sums only the visable cells
> (non-hidden) rows. Any suggestions? (The rows are hidden and not filtered).
> --
> Thanks Buuuuddddy!

[Paully Shore]

Dave, thanks for your quick response but when I tried that formula, I got
#VALUE! as a result. I am familiar with =SUBTOTAL(9,A1:A8000) but what is
"109". I cannot get that to work.
--
Thanks Buuuuddddy!


"David Billigmeier" wrote:

> =SUBTOTAL(109,A1:A8000) will give you what you need.
>
>
> --
> Regards,
> Dave
>
>
> "Paully Shore" wrote:
>
> > I have a worksheet with 8000+ rows. Some of the rows are hidden. I want to
> > have a formula at the bottom of the rows that sums only the visable cells
> > (non-hidden) rows. Any suggestions? (The rows are hidden and not filtered).
> > --
> > Thanks Buuuuddddy!

[David Billigmeier]

What version of Excel are you using? "109" should ignore all hidden values,
where as just "9" includes hidden values (in 2003 atleast)

--
Regards,
Dave


"Paully Shore" wrote:

> Dave, thanks for your quick response but when I tried that formula, I got
> #VALUE! as a result. I am familiar with =SUBTOTAL(9,A1:A8000) but what is
> "109". I cannot get that to work.
> --
> Thanks Buuuuddddy!
>
>
> "David Billigmeier" wrote:
>
> > =SUBTOTAL(109,A1:A8000) will give you what you need.
> >
> >
> > --
> > Regards,
> > Dave
> >
> >
> > "Paully Shore" wrote:
> >
> > > I have a worksheet with 8000+ rows. Some of the rows are hidden. I want to
> > > have a formula at the bottom of the rows that sums only the visable cells
> > > (non-hidden) rows. Any suggestions? (The rows are hidden and not filtered).
> > > --
> > > Thanks Buuuuddddy!

[Paully Shore]

Ahh, that may be my problem. I am using Excel 2002 version here. Is there
anyway you (or anyone) know of to do this with the 2002 version. thanks
again!!!
--
Thanks Buuuuddddy!


"David Billigmeier" wrote:

> What version of Excel are you using? "109" should ignore all hidden values,
> where as just "9" includes hidden values (in 2003 atleast)
>
> --
> Regards,
> Dave
>
>
> "Paully Shore" wrote:
>
> > Dave, thanks for your quick response but when I tried that formula, I got
> > #VALUE! as a result. I am familiar with =SUBTOTAL(9,A1:A8000) but what is
> > "109". I cannot get that to work.
> > --
> > Thanks Buuuuddddy!
> >
> >
> > "David Billigmeier" wrote:
> >
> > > =SUBTOTAL(109,A1:A8000) will give you what you need.
> > >
> > >
> > > --
> > > Regards,
> > > Dave
> > >
> > >
> > > "Paully Shore" wrote:
> > >
> > > > I have a worksheet with 8000+ rows. Some of the rows are hidden. I want to
> > > > have a formula at the bottom of the rows that sums only the visable cells
> > > > (non-hidden) rows. Any suggestions? (The rows are hidden and not filtered).
> > > > --
> > > > Thanks Buuuuddddy!

[Dave Peterson]

You can use a userdefined function:

Option Explicit
Function SumVisible(rng As Range) As Double

Application.Volatile True

Dim myCell As Range
Dim mySum As Double

mySum = 0
For Each myCell In rng.Cells
If myCell.RowHeight = 0 _
Or myCell.ColumnWidth = 0 Then
'do nothing
Else
If Application.IsNumber(myCell.Value) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell

SumVisible = mySum

End Function

When I hid/unhid a column, the UDF didn't recalculate (xl2003). You may want to
force a recalc before you trust the results.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumvisible(a1:a8000)



Paully Shore wrote:
>
> I have a worksheet with 8000+ rows. Some of the rows are hidden. I want to
> have a formula at the bottom of the rows that sums only the visable cells
> (non-hidden) rows. Any suggestions? (The rows are hidden and not filtered).
> --
> Thanks Buuuuddddy!

--

Dave Peterson

[Paully Shore]

You rule!! This did the trick. I couldn't figure out how to use a macro to
recogize a hidden cell. I didn't think of using "myCell.RowHeight = 0".
Thanks again!!!
--
Thanks Buuuuddddy!


"Dave Peterson" wrote:

> You can use a userdefined function:
>
> Option Explicit
> Function SumVisible(rng As Range) As Double
>
> Application.Volatile True
>
> Dim myCell As Range
> Dim mySum As Double
>
> mySum = 0
> For Each myCell In rng.Cells
> If myCell.RowHeight = 0 _
> Or myCell.ColumnWidth = 0 Then
> 'do nothing
> Else
> If Application.IsNumber(myCell.Value) Then
> mySum = mySum + myCell.Value
> End If
> End If
> Next myCell
>
> SumVisible = mySum
>
> End Function
>
> When I hid/unhid a column, the UDF didn't recalculate (xl2003). You may want to
> force a recalc before you trust the results.
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Short course:
>
> Open your workbook.
> Hit alt-f11 to get to the VBE (where macros/UDF's live)
> hit ctrl-R to view the project explorer
> Find your workbook.
> should look like: VBAProject (yourfilename.xls)
>
> right click on the project name
> Insert, then Module
> You should see the code window pop up on the right hand side
>
> Paste the code in there.
>
> Now go back to excel.
> Into a test cell and type:
> =sumvisible(a1:a8000)
>
>
>
> Paully Shore wrote:
> >
> > I have a worksheet with 8000+ rows. Some of the rows are hidden. I want to
> > have a formula at the bottom of the rows that sums only the visable cells
> > (non-hidden) rows. Any suggestions? (The rows are hidden and not filtered).
> > --
> > Thanks Buuuuddddy!
>
> --
>
> Dave Peterson
>