software

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!

=SUBTOTAL(109,A1:A8000) will give you what you need.--
Regards,
Davequot;Paully Shorequot; wrote:

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

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
quot;109quot;. I cannot get that to work.
--
Thanks Buuuuddddy!quot;David Billigmeierquot; wrote:

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

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

--
Regards,
Davequot;Paully Shorequot; wrote:

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

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!quot;David Billigmeierquot; wrote:

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

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:
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:
gt;
gt; I have a worksheet with 8000 rows. Some of the rows are hidden. I want to
gt; have a formula at the bottom of the rows that sums only the visable cells
gt; (non-hidden) rows. Any suggestions? (The rows are hidden and not filtered).
gt; --
gt; Thanks Buuuuddddy!

--

Dave Peterson

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 quot;myCell.RowHeight = 0quot;.
Thanks again!!!
--
Thanks Buuuuddddy!quot;Dave Petersonquot; wrote:

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