View Full Version : [SOLVED:] Excel 2013>VBA>Function>Sum Visible Columns
aworthey
06-03-2016, 09:12 AM
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
06-03-2016, 09:16 AM
I would like this to automatically update values in the same way that the SUBTOTAL formula does. Thank you.
mdmackillop
06-03-2016, 01:55 PM
There is no direct event, but maybe
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub
aworthey
06-03-2016, 02:05 PM
Thanks, mdmackillop! That works exactly the way I want it to work. What a simple solution!!
aworthey
06-03-2016, 02:08 PM
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
06-03-2016, 02:13 PM
I've tried disabling:
If cell.Rows.Hidden = False Then from the Sum_Visible_Cells Function, but that has no effect.
mdmackillop
06-03-2016, 02:27 PM
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
06-03-2016, 02:31 PM
I finally figured it out:
If (cell.Rows.Hidden = False) And (cell.Columns.Hidden = False) Then
aworthey
06-03-2016, 02:33 PM
Mdmackillop, thank you very much for your help! I'll look next at your suggestions for abbreviating the code. Have a great weekend!
mdmackillop
06-03-2016, 02:46 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.