Consulting

Results 1 to 10 of 10

Thread: Excel 2013>VBA>Function>Sum Visible Columns

  1. 06-03-2016, 09:12 AM #1
    aworthey
    aworthey is offline
    VBAX Regular
    Joined
    May 2016
    Posts
    73
    Location

    Excel 2013>VBA>Function>Sum Visible Columns

    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
    Reply With Quote Reply With Quote
  2. 06-03-2016, 09:16 AM #2
    aworthey
    aworthey is offline
    VBAX Regular
    Joined
    May 2016
    Posts
    73
    Location
    I would like this to automatically update values in the same way that the SUBTOTAL formula does. Thank you.
    Reply With Quote Reply With Quote
  3. 06-03-2016, 01:55 PM #3
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base     VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    There is no direct event, but maybe
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  4. 06-03-2016, 02:05 PM #4
    aworthey
    aworthey is offline
    VBAX Regular
    Joined
    May 2016
    Posts
    73
    Location
    Thanks, mdmackillop! That works exactly the way I want it to work. What a simple solution!!
    Reply With Quote Reply With Quote
  5. 06-03-2016, 02:08 PM #5
    aworthey
    aworthey is offline
    VBAX Regular
    Joined
    May 2016
    Posts
    73
    Location
    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
    Reply With Quote Reply With Quote
  6. 06-03-2016, 02:13 PM #6
    aworthey
    aworthey is offline
    VBAX Regular
    Joined
    May 2016
    Posts
    73
    Location
    I've tried disabling: 
    If cell.Rows.Hidden = False Then
    from the Sum_Visible_Cells Function, but that has no effect.
    Reply With Quote Reply With Quote
  7. 06-03-2016, 02:27 PM #7
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  8. 06-03-2016, 02:31 PM #8
    aworthey
    aworthey is offline
    VBAX Regular
    Joined
    May 2016
    Posts
    73
    Location
    I finally figured it out:

    If (cell.Rows.Hidden = False) And (cell.Columns.Hidden = False) Then
    Reply With Quote Reply With Quote
  9. 06-03-2016, 02:33 PM #9
    aworthey
    aworthey is offline
    VBAX Regular
    Joined
    May 2016
    Posts
    73
    Location
    Mdmackillop, thank you very much for your help! I'll look next at your suggestions for abbreviating the code. Have a great weekend!
    Reply With Quote Reply With Quote
  10. 06-03-2016, 02:46 PM #10
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules