sum function exception hidden cells
Posted by gokhan caglayan on August 13, 2001 7:20 AM
How can i get a sum of a range, except the hidden cells. Like in the status bar, you can see a sum of only the visible cells.
Posted by Mark W. on August 13, 2001 7:32 AM
How were they hidden?
Posted by gokhan caglayan on August 13, 2001 11:50 PM
First of all Aladdin told me to ask you. Because the solution is by the VBA he said.
I hide them. I have a big file and every month i update it. There are a lot of empty cells so i hide them. But next month they can have numbers because they are linked. So every time i have to unhide and check if they have numbers or not. I need a function that only get sum of the visible cells not the unhided ones. As the function in status bar.
If i filter the empty cells, i can have the sum with SUBTOTAL function by excluding the hided cells . But ý have to hide the rows manually so i can not use the subtotal function.
Posted by Ivan F Moala on August 14, 2001 1:13 AM
This UDF may help you
Function SumVisibleCells(SumRg As Range)
Application.Volatile
Dim sCell As Range
Dim s
For Each sCell In SumRg
If sCell.EntireRow.Hidden = False Then
s = s + sCell
End If
Next
SumVisibleCells = s
End Function
Ivan
Posted by Aladin Akyurek on August 14, 2001 3:43 AM
Gokhan has indeed informed me about his problem in an e-mail in my native tongue, whence this continuation.
I wonder if you could help him to hide/unhide automatically thru VBA, something that is like what 'faster' did at
26805.html
integrated with your UDF?
Aladin
Posted by Mark W. on August 14, 2001 7:25 AM
I don't understand...
...why doesn't Gokhan just use AutoFilter? The
same logic needed to hide/unhide via VBA could be
incorporated into a formula whose results are
then filtered. If Gokhan doesn't want to see
the column containing this formula it can be
hidden. 26805.html integrated with your UDF? : This UDF may help you : Function SumVisibleCells(SumRg As Range) : Application.Volatile : Dim sCell As Range : Dim s : For Each sCell In SumRg : If sCell.EntireRow.Hidden = False Then : s = s + sCell : End If : Next : SumVisibleCells = s : End Function
Posted by Aladin Akyurek on August 14, 2001 7:46 AM
Re: I don't understand...
same logic needed to hide/unhide via VBA could be incorporated into a formula whose results are then filtered. If Gokhan doesn't want to see the column containing this formula it can be hidden.
Neither do I. My suggestion has been to make the process he describes fully automatic, triggered by changes in the range where he got his numbers. : Gokhan has indeed informed me about his problem in an e-mail in my native tongue, whence this continuation. : I wonder if you could help him to hide/unhide automatically thru VBA, something that is like what 'faster' did at : 26805.html : integrated with your UDF? : Aladin
