Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing visable cells only
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing visable cells only
=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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing visable cells only
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing visable cells only
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing visable cells only
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing visable cells only
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing visable cells only
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing only those cells that aren't hidden | Excel Worksheet Functions | |||
To clear visable cells in a subtotal - can it be done? | Excel Worksheet Functions | |||
summing up contents on ceratin cells depending on selection criter | Excel Worksheet Functions | |||
Help adding text values | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |