Grubeaters
asked on
Sum Visible Columns or Rows in Range
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
End If
End If
Next
SumVisible = value
End Function
after that on your sheet you can use this function:
=SumVisible("A1:D1")
=SumVisible("B1:B20")
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
End If
End If
Next
SumVisible = value
End Function
after that on your sheet you can use this function:
=SumVisible("A1:D1")
=SumVisible("B1:B20")
If you have a filtered list (Data|Filter|Autofilter), then use the SUBTOTAL function #9. Otherwise the function above must be adapted. Please state whether the ranges G2 and I2:K2 contains numbers.
ASKER
The function that I posted will sum only visible columns or rows if they are consecutive. For example, =sumvisible(A1:D1) sums cells A1, B1, C1 and D1 only if not hidden. I want to have the function work on non-consecutive columns or rows so for example, =sumvisible(A1, D1, F1, Z1) would sum cells A1, D1, F1 and Z1 only if if they are not hidden.
I forgot to add the appilaction.volatile so the function looks like this and the F9 key works:
Public Function SumVisible(a As Range) As Double
Dim c As Range
Dim value As Double
Application.Volatile
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
End If
End If
Next
SumVisible = value
End Function
---------------------
To answer the other comment about G2 and I2:K2 do contain numbers and/or blanks.
I forgot to add the appilaction.volatile so the function looks like this and the F9 key works:
Public Function SumVisible(a As Range) As Double
Dim c As Range
Dim value As Double
Application.Volatile
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
End If
End If
Next
SumVisible = value
End Function
---------------------
To answer the other comment about G2 and I2:K2 do contain numbers and/or blanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
pauloaguia, that is truly neat. Did not find this trick in the net, yet.
BTW, this hit came up, is only closely related, but could be useful: http://www.cof.orst.edu/net/software/excel/tips/#SelectVisible
BTW, this hit came up, is only closely related, but could be useful: http://www.cof.orst.edu/net/software/excel/tips/#SelectVisible
ASKER
Thank you. Excellent! It works great.
Thansk for the points. This is one of those questions I enjoyed because I learnt something from it...
More on this:
Your original function was OK after all. Just found out today that all you needed was to use it like so:
=SUMVisible((A1:C1;F1:I1))
OR
=SUMVisible((A1:C1,F1:I1))
(depending on regional settings)
Just imagine. If I'd known this in the first place we'd never have found out about that ParamArray thing... :)
And now we've learned that you can use () to define combined ranges.... neat :)
Your original function was OK after all. Just found out today that all you needed was to use it like so:
=SUMVisible((A1:C1;F1:I1))
OR
=SUMVisible((A1:C1,F1:I1))
(depending on regional settings)
Just imagine. If I'd known this in the first place we'd never have found out about that ParamArray thing... :)
And now we've learned that you can use () to define combined ranges.... neat :)
The only thing I'm finding strange is that it doesn't recalculate when you hide a column. Not even if I press F9. Only when I change one of the values in the range or even the formula.
But asides from that the function is doing what you asked.
Paulo