Sum Visible Columns or Rows in Range
Posted on 2003-03-07
I came across this user defined fuction on this site. I would like to perhaps modify the below function to work like the SUM function. For example, if my formula says =Sumvisible(E2, F2, H2, L2), it would add those cells that are visible. If I hide Column F, then my formula should recalculate with E2, H2, and L2. Is this possible or is there a better solution?
You can use user-defined function.
Press Alt-F11 (open VBA editor), add module and copy-paste next code:
Public Function SumVisible(a As Range) As Double
Dim c As Range
Dim value As Double
value = 0
For Each c In a
If c.EntireColumn.Hidden = False And c.EntireRow.Hidden = False Then
If IsNumeric(c.value) = True Then
value = value + c.value
SumVisible = value
after that on your sheet you can use this function: