Adding non-hidden data
Posted by Kurt on April 13, 2001 7:51 AM
Is there a way to ask Excel if cells in a particular column or row are hidden, and if so add only the cells that are not hidden?
Posted by Mark W. on April 13, 2001 9:52 AM
By "hidden" do you mean filtered? Have you looked
at Excel's SUBTOTAL() function?
Posted by Kurt on April 13, 2001 1:07 PM
No not filtered. What I mean is that I've selected a group rows in the middle of columns of data. There is data I want to add above and below the hidden rows, but not include those rows that are hidden in the calculation.
Thanks a lot Mark.
Kurt
Posted by Mark W. on April 13, 2001 1:47 PM
Kurt, you could always "tag" those rows that you
wanted to remain "visible". This column could
contain a 0 or 1. Let's say 1 means "remain
visible". You could filter on the 1's in this
new column and use =SUBTOTAL(9,ref) to do the
addition.
Posted by RS on April 13, 2001 5:39 PM
Here's a UDF from J. Walkenbach's book "Excel 2000 Formulas"
Function COUNTVISIBLE(rng)
' Counts visible cells
Dim CellCount As Long
Dim cell As Range
Application.Volatile
CellCount = 0
Set rng = Intersect(rng.Parent.UsedRange, rng)
For Each cell In rng
If Not IsEmpty(cell) Then
If Not cell.EntireRow.Hidden And _
Not cell.EntireColumn.Hidden Then _
CellCount = CellCount + 1
End If
Next cell
COUNTVISIBLE = CellCount
End Function
Posted by Dave Hawley on April 14, 2001 8:11 PM
RS' formula is the one!
Kurt
Kurt, If you use Marks method you may as well just add them manually. Therefore I would definately go with the UDF that RS proposed.
Dave
OzGrid Business Applications
Posted by Kurt on May 15, 2001 6:46 AM
Mark,
What I did before reading your reply was to change to format of the hiddend data to include decimal points and then using the CELL function have columns to the right tell me what type of format each cell had. From their I used sumif formulas. I think you way is more efficient.
You're a great help as always.
Kurt
