Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Summing Hidden Cells

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm trying to enter a formula in a macro that will sum all hidden cells within a particular column, is this possible? If so, can someone supply the code?

    Many thanks

    Matt

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Matt

    I could write you a UDF for this but quite frankly thed formula below would be FAR more efficient

    =SUM($A$1:$A$10000)-SUBTOTAL(9,$A$1:$A$10000)

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dave, I have actually hidden rows through using code,

    Dim iRow As Long
    iRow = 2
    Do While Cells(iRow, 1) <> ""
    If Cells(iRow, 1) <> "Other" Then
    Rows(iRow & ":" & iRow).Hidden = True
    End If
    iRow = iRow + 1

    The formula you suggested does not appear to be working. Will the formula only work if the hidden cells are filtered rather than hidden through code. If so, do you have any other suggestions.

    Thanks again

    Matt

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, the SUBTOTAL will only work on Filtered cells (which truth be known you should use in you code). But anyway use this to hide the rows:

    Dim iRow As Long
    iRow = 2

    For iRow = 2 To Range("A2", Range("A2").End(xlDown)).Row
    Rows(iRow & ":" & iRow).Hidden = (Cells(iRow, 1) = "Other")
    Next i

    If you don't get your UDF by the morning let me know.



  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave

    Sorry to be a complete pain in the ****, Have tried to hide the cells as you suggested and have the following error on the last line "Next i"

    "invalid next control variable reference", any ideas?

    thanks

    Matt

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try with this... But still i think Dave has give good solution by using builtin formula..

    Function visiblecelltotal(totalrange As Range) As Double
    Dim c As Range
    Application.Volatile

    For Each c In totalrange

    ' set value to TRUE if you want total of
    ' hidden rows


    If c.EntireRow.Hidden = False Then

    visiblecelltotal = visiblecelltotal + c.Value

    End If

    Next c

    End Function

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Matt, my fault

    Dim iRow As Long

    For iRow = 2 To Range("A1").End(xlDown).Row
    Rows(iRow).EntireRow.Hidden = (Cells(iRow, 1) = "Other")
    Next iRow


    May need to change the range references to suit.



  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Dave

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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