Excel 2013>VBA>Function>Sum Visible Columns

[aworthey]

Hello,

I'm creating a cost worksheet where I need to sum visible columns and visible rows. I'm using the subtotal formula to sum vertically. But I need a solution to sum horizontally. I'm using the attached code, and it's working perfectly when I call for it inside the cell as an Excel formula with range specified. But, in order to update values, I have to click in cell then click in formula bar which highlights range then hit enter.

Is there a way to automatically recalculate values whenever columns are hidden or unhidden?

Thanks!


Option Explicit


Function SumVisible(r As Range) As Double
Dim rCell As Range


Application.Volatile


Dim cell As Range


For Each rCell In r.Cells
With rCell
If Not .Rows.Hidden And Not .Columns.Hidden Then SumVisible = SumVisible + .Value
End With
Next


End Function

[aworthey]

I would like this to automatically update values in the same way that the SUBTOTAL formula does. Thank you.

[mdmackillop]

There is no direct event, but maybe

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub

[aworthey]

Thanks, mdmackillop! That works exactly the way I want it to work. What a simple solution!!

[aworthey]

I am now encountering another problem. I have code to hide rows and columns based on cell values...and it's working fine except the HideRows macro is preventing Sum_Visible_Cells from functioning.

Here's the code:

Option Explicit



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("$A$1").Value = 1 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = True
Columns("D").EntireColumn.Hidden = True
Columns("E").EntireColumn.Hidden = True
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 2 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = True
Columns("E").EntireColumn.Hidden = True
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 3 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = True
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 4 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 5 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 6 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 7 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 8 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 9 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 10 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
Columns("K").EntireColumn.Hidden = False
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 11 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
Columns("K").EntireColumn.Hidden = False
Columns("L").EntireColumn.Hidden = False
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 12 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
Columns("K").EntireColumn.Hidden = False
Columns("L").EntireColumn.Hidden = False
Columns("M").EntireColumn.Hidden = False
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 13 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
Columns("K").EntireColumn.Hidden = False
Columns("L").EntireColumn.Hidden = False
Columns("M").EntireColumn.Hidden = False
Columns("N").EntireColumn.Hidden = False
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 14 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
Columns("K").EntireColumn.Hidden = False
Columns("L").EntireColumn.Hidden = False
Columns("M").EntireColumn.Hidden = False
Columns("N").EntireColumn.Hidden = False
Columns("O").EntireColumn.Hidden = False
Columns("P").EntireColumn.Hidden = True
ElseIf Range("$A$1").Value = 15 Then
Columns("B").EntireColumn.Hidden = False
Columns("C").EntireColumn.Hidden = False
Columns("D").EntireColumn.Hidden = False
Columns("E").EntireColumn.Hidden = False
Columns("F").EntireColumn.Hidden = False
Columns("G").EntireColumn.Hidden = False
Columns("H").EntireColumn.Hidden = False
Columns("I").EntireColumn.Hidden = False
Columns("J").EntireColumn.Hidden = False
Columns("K").EntireColumn.Hidden = False
Columns("L").EntireColumn.Hidden = False
Columns("M").EntireColumn.Hidden = False
Columns("N").EntireColumn.Hidden = False
Columns("O").EntireColumn.Hidden = False
Columns("P").EntireColumn.Hidden = False
Else
Columns("B").EntireColumn.Hidden = True
Columns("C").EntireColumn.Hidden = True
Columns("D").EntireColumn.Hidden = True
Columns("E").EntireColumn.Hidden = True
Columns("F").EntireColumn.Hidden = True
Columns("G").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = True
Columns("I").EntireColumn.Hidden = True
Columns("J").EntireColumn.Hidden = True
Columns("K").EntireColumn.Hidden = True
Columns("L").EntireColumn.Hidden = True
Columns("M").EntireColumn.Hidden = True
Columns("N").EntireColumn.Hidden = True
Columns("O").EntireColumn.Hidden = True
Columns("P").EntireColumn.Hidden = True
End If

'If Range("$A$2").Value = "No" Then
'Rows("6").EntireRow.Hidden = True
'Rows("7").EntireRow.Hidden = True
'Rows("8").EntireRow.Hidden = True
'Rows("9").EntireRow.Hidden = True
'Rows("10").EntireRow.Hidden = True
'Rows("11").EntireRow.Hidden = True
'Rows("12").EntireRow.Hidden = True
'Rows("13").EntireRow.Hidden = True
'ElseIf Range("$A$2").Value = "Yes" Then
'Rows("6").EntireRow.Hidden = False
'Rows("7").EntireRow.Hidden = False
'Rows("8").EntireRow.Hidden = False
'Rows("9").EntireRow.Hidden = False
'Rows("10").EntireRow.Hidden = False
'Rows("11").EntireRow.Hidden = False
'Rows("12").EntireRow.Hidden = False
'Rows("13").EntireRow.Hidden = False
'End If

Calculate

End Sub



Option Explicit


Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim cell As Object
Dim Total As Double
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
Total = Total + cell.Value
End If
End If
Next
Sum_Visible_Cells = Total
End Function

[aworthey]

I've tried disabling:
If cell.Rows.Hidden = False Then from the Sum_Visible_Cells Function, but that has no effect.

[mdmackillop]

I'll have a look. Meantime, to abbreviate the code try

Private Sub Worksheet_Change(ByVal Target As Range)
ShowHide
Calculate
End Sub

Sub ShowHide()
Columns("B:P").EntireColumn.Hidden = True
Columns("B").Resize(, Range("$A$1").Value).EntireColumn.Hidden = False
End Sub

[aworthey]

I finally figured it out:


If (cell.Rows.Hidden = False) And (cell.Columns.Hidden = False) Then

[aworthey]

Mdmackillop, thank you very much for your help! I'll look next at your suggestions for abbreviating the code. Have a great weekend!

[mdmackillop]

Happy to help.

To include hiding rows

Sub ShowHide()
Columns("B:P").EntireColumn.Hidden = True
Columns("B").Resize(, Range("$A$1").Value).EntireColumn.Hidden = False
Rows("6:13").EntireRow.Hidden = (UCase(Range("A2").Formula) = "NO")
End Sub