Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing hidden fields from equation?
I have a simple equation like =SUM(F1:F28) but how can I prevent hidden cells from being added? -- djarcadian ------------------------------------------------------------------------ djarcadian's Profile: http://www.excelforum.com/member.php...o&userid=15877 View this thread: http://www.excelforum.com/showthread...hreadid=535727 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing hidden fields from equation?
If the row is hidden as the result of an autofilter, you could use:
=subtotal(9,f1:f28) If you're using xl2003, you could use: =subtotal(109,f1:f28) And that will ignore hidden rows -- both filtered and manually hidden. ====== Or you could 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 djarcadian wrote: I have a simple equation like =SUM(F1:F28) but how can I prevent hidden cells from being added? -- djarcadian ------------------------------------------------------------------------ djarcadian's Profile: http://www.excelforum.com/member.php...o&userid=15877 View this thread: http://www.excelforum.com/showthread...hreadid=535727 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing The N/A from showing in open fields | Excel Worksheet Functions | |||
removing leading zeros in numeric fields | Excel Discussion (Misc queries) | |||
removing digit or calc LEN fields | Excel Worksheet Functions | |||
Copy without Hidden Cols - How | Excel Discussion (Misc queries) | |||
Removing Hidden But Keep Values | Excel Discussion (Misc queries) |